procedure过程触发器trigger_procedure触发器-优快云博客

存储过程

create or replace procedure a(j out int)
as
begin
j:=0;
for i in 1..100 loop
j:=j+i;
end loop;
end;

set serveroutput on
declare
suma int;
begin
a(suma);
dbms_output.put_line(suma);
end;

事务

事务是单个的工作单元。如果某一事务成功，则在该事务中进行的所有数据更改均会提交，成为数据库中的永久组成部分。如果事务遇到错误且必须取消或回滚，则所有数据更改均被清除。

oracle

sqlserver

触发器

触发器是一种特殊类型的存储过程，不由用户直接调用。创建触发器时会对其进行定义，以便在对特定表或列作特定类型的数据修改时执行。

drop table super;
drop table sub;
create table super(bh int primary key,age int);
insert into super values(1,22);
insert into super values(2,23);
create table sub(id int primary key,sm char(30),bh int ,foreign key(bh) references super(bh));
insert into sub values(1,'os',2);
insert into sub values(2,'db',2);
insert into sub values(3,'db',1);
insert into sub values(4,'linux',1);

create or replace trigger tri_del after delete on super
for each row
begin
delete from sub where bh=:old.bh;
end;
　

--drop object
drop trigger mytri;
drop table student;
drop table borrowrecord;

--create table
create table student(studentid int,name char(20));
insert into student values (1,'abc');
insert into student values (1,'abc');
insert into student values (2,'abc');
insert into student values (3,'abc');

create table borrowrecord (borrowid int,studentid int);
insert into borrowrecord values (1,1);
insert into borrowrecord values (2,1);
insert into borrowrecord values (3,2);
insert into borrowrecord values (4,3);

--create trigger
■SqlServer
create trigger mytri on student for delete
as
delete borrowrecord from borrowrecord br,deleted d where br.studentid=d.studentid

■Oracle
create trigger mytri after delete on student
for each row
begin
delete from borrowrecord br where br.studentid=:old.studentid;
end;

■Mysql5.0
delimiter |
create trigger mytri after delete on student
for each row
begin
delete from borrowrecord where borrowrecord.studentid=old.studentid;
end;
|
或
mysql> delimiter | 设定分隔符
mysql> create trigger beforeinsertuserinfo
-> before insert on userinfo
-> for each row
-> begin
-> insert into userinfolog values(now(),CONCAT(new.userid,new.username));
-> end;
-> |

mysql> delimiter ; 恢复；分隔符
mysql> show triggers;
或
create trigger mytri after delete on student
for each row
delete from borrowrecord where borrowrecord.studentid=old.studentid;

--test trigger
select * from student;
select * from borrowrecord;
delete from student where studentid=1;
select * from student;
select * from borrowrecord;

--显示触发器
mysql: SELECT trigger_name,trigger_schema FROM information_schema.TRIGGERS T; show triggers
oracle:select trigger_name from user_triggers
sqlserver:sp_helptrigger tablename