Comparison of the use of triggers in Oracle and the use of triggers in mysql

Mysql relational database management system

MySQL is an open source small relational database management system developed by the Swedish MySQL AB company. MySQL is widely used in small and medium-sized websites on the Internet. Due to its small size, fast speed, low total cost of ownership, and especially the characteristics of open source, many small and medium-sized websites choose MySQL as their website database in order to reduce the total cost of website ownership.

This article mainly introduces the case comparison of Oracle's use of triggers and mysql's use of triggers. This article explains it through examples and gives you a very detailed introduction. Friends who need it Refer to the following

1. Triggers

1. Triggers are stored as independent objects in the database,

2. Triggers do not need to be called, they are An event triggers the operation

　3. Triggers cannot receive parameters

　--Trigger application

　For example: Xiaonei.com, Kaixin.com, Facebook, when you Post a log and automatically notify friends. In fact, when adding a log, you make a start and then write entries to the table.

　--The efficiency of triggers is very high

For example: For forum postings, every time a post is inserted, you want to synchronize the last posting time and total number of posts fields in the forum table. At this time Using triggers will be very efficient.

2. Oracle uses PL/SQL to write triggers

1.--General syntax for creating triggers in PL/SQL

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.--Exercise

--问题3.使用:old 和 :new 操作符
create or replace trigger tri_update
after
update on employees
for each row 
begin
　　dbms_output.put_line('更新前：'||:old.salary||' 更新后：'||:new.salary);
end;
--问题2.编写一个触发器，在向 emp 表中插入记录时 打印'hello'
create or replace trigger tri_update
after
insert on emp
begin
　　dbms_output.put_line('ok');
end;
--问题1.一个helloworld级别的触发器
--创建一个触发器,在更新employees表的时候触发
create or replace trigger tri_update
after
update on employees
for each row --想在最后执行完打印一个ok，把这句话去掉
begin
　　dbms_output.put_line('ok');
end;
--执行
update employees
set salary = salary+1
where department_id = 80

3. Using triggers in MySql

--假设有两张表 board 和 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)
);
--创建一个触发器
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 ;
--当我们对article表执行插入操作的是后就会触发这个触发器
insert into board values(null,'test_boardname',0);
insert into article values(null,'test_title',1);
--执行完这条插入语句后，board表中的articleCount字段值回+1;这个操作由触发器完成。

The above is the comparison of cases of using triggers in Oracle and using triggers in mysql. For more related content, please pay attention to the PHP Chinese website (www.php. cn)!