Oracle using triggers and comparisons using triggers in MySQL-Learn notes

Source: Internet
Author: User

First, Trigger

1. Triggers are stored in the database as separate objects,
2. Triggers do not need to be called, it is triggered by an event to run
3. Triggers cannot receive parameters

  --Application of Trigger
For example: Xiaonei, Happy Net, Facebook, when you send a log, automatically notify your friends, in fact, when you add a log to do a start, and then write entries to the table.

  --Trigger efficiency is high
Example: forum posts, each insert a post will want to the layout table in the last posting time, the total number of posts to synchronize updates, when the use of trigger efficiency will be very high.

Ii. Oracle writing triggers using PL/SQL

1.--pl/sql general syntax for creating triggers
create [or replace] trigger Trigger_name
{before | after}
{Insert | delete | update [of column[,column ...]} On table_name
[For each row]
[Where Condition]
--trigger_body;
Begin
End

2.--Practice

--Question 3. Using: Old and: New operator
Create or Replace Trigger Tri_update
After
Update on employees
For each row
Begin
Dbms_output.put_line (' Before update: ' | |:o ld.salary| | ' Updated: ' | |:new.salary);
End

--Question 2. Write a trigger to print ' Hello ' when inserting records into the EMP table
Create or Replace Trigger Tri_update
After
Insert on EMP
Begin
Dbms_output.put_line (' OK ');
End


--Question 1. A HelloWorld level trigger
--Create a trigger that fires when the Employees table is updated
Create or Replace Trigger Tri_update
After
Update on employees
For each row--you want to print an OK at the end of the last execution, remove this sentence
Begin
Dbms_output.put_line (' OK ');
End
-Execution
Update Employees
Set salary = Salary+1
where department_id = 80

Third, use the trigger in MySQL

--Suppose there are two tables board and article
CREATE TABLE Board (
ID int primary KEY auto_increment,
Name varchar (50),
Articlecount int
);

CREATE TABLE article (
ID int primary KEY auto_increment,
Title varchar (50),
Bid int references Board (ID)
);

--Create a trigger

Delimiter $$

Create Trigger Insertarticle_trigger
After insert on article
For each row
Begin
Update board Set articlecount=articlecount+1
where id = new.bid;
End
$$

delimiter;


--This trigger is triggered when we perform an insert operation on the article table
INSERT into board values (null, ' Test_boardname ', 0);

INSERT into article values (NULL, ' Test_title ', 1);
--After executing this INSERT statement, the value of the Articlecount field in the board table is returned to +1; This operation is done by the trigger.

Oracle using triggers and comparisons using triggers in MySQL-Learn notes

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.