触发器(trigger)是个特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,比如当对一个表进行操作( insert,delete, update)时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从 DBA_TRIGGERS ,USER_TRIGGERS 数据字典中查到。
触发器与存储过程的唯一区别是触发器不能执行EXECUTE语句调用,而是在用户执行Transact-SQL语句时自动触发执行。
创建SQL语法
DELIMITER |
CREATE TRIGGER `<databaseName>`.`<triggerName>`
< [ BEFORE | AFTER ] > < [ INSERT | UPDATE | DELETE ] >
ON [dbo]<tableName> //dbo代表该表的所有者
FOR EACH ROW
BEGIN
--do something
END
create table Student
(
StudentID int primary Key
)
Create Table BorrowRecord
( --学生借书记录表
BorrowRecord int identity(1,1), --流水号
StudentID int , --学号
BorrowDate datetime, --借出时间
ReturnDAte Datetime, --归还时间
)
create trigger truStudent--触发器更新
on Student
for update
as
if update(StudentID)
BEGIN
UPDATE BorrowRecord
SET StudentID=i.StudentID
FROM BorrowRecord br,Deleted d,Inserted i
where br.StudentID=d.StudentID
end
----Deleted,Inserted是两张临时表,Deleted代表旧表,Inserted代表新更新的记录表
Create trigger trdStudent--触发器删除
On Student
for Delete
As
Delete BorrowRecord
From BorrowRecord br , Delted d
Where br.StudentID=d.StudentID
create trigger triStudent--触发器插入
on Student
for insert
as
insert into BorrowRecord(StudentID)
select i.StudentID
FROM Inserted i
DELIMITER |
CREATE TRIGGER `<databaseName>`.`<triggerName>`
< [ BEFORE | AFTER ] > < [ INSERT | UPDATE | DELETE ] >
ON [dbo]<tableName> //dbo代表该表的所有者
FOR EACH ROW
BEGIN
--do something
END
create table Student
(
StudentID int primary Key
)
Create Table BorrowRecord
( --学生借书记录表
BorrowRecord int identity(1,1), --流水号
StudentID int , --学号
BorrowDate datetime, --借出时间
ReturnDAte Datetime, --归还时间
)
create trigger truStudent--触发器更新
on Student
for update
as
if update(StudentID)
BEGIN
UPDATE BorrowRecord
SET StudentID=i.StudentID
FROM BorrowRecord br,Deleted d,Inserted i
where br.StudentID=d.StudentID
end
----Deleted,Inserted是两张临时表,Deleted代表旧表,Inserted代表新更新的记录表
Create trigger trdStudent--触发器删除
On Student
for Delete
As
Delete BorrowRecord
From BorrowRecord br , Delted d
Where br.StudentID=d.StudentID
create trigger triStudent--触发器插入
on Student
for insert
as
insert into BorrowRecord(StudentID)
select i.StudentID
FROM Inserted i