SQL SERVER触发器回顾（联级删除）

一个简单触发器的结构

CREATE TRIGGER trigger_name

ON table_name

[WITH ENCRYPTION]

FOR | AFTER | INSTEAD OF [DELETE,
INSERT, UPDATE]

AS

T-SQL语句

GO

删除数据（联级）

create trigger tri_test

on StuInfo

instead of delete

as

begin

declare @id varchar(15)

select @id=StuID from deleted

delete from Achievement where StuID=@id

delete from StuInfo where StuID=@id

end

go

delete from StuInfo where StuID=‘HB0009’

instead of :是数据库的“导火线”，触发器是否能执行到想要的结果，需要由触发器中的逻辑进行判断，因为该触发器往往伴随着很多的判断分支。

例子:

CREATE TRIGGER T_B

ON table_a

instead of insert

as

begin

IF EXISTS(select*from table_a where name
=(select name from inserted))

ROLLBACK TRANSACTION

PRINT ‘该姓名已存在’

ELSE

INSERT INTO table_a VALUES(select*from inserted)

PRINT ‘插入成功’

end

after: 操作成功后（这里是插入table_a后），执行触发器操作

例如

CREATE TRIGGER
T_A

ON table_a

after insert

as

begin

declare @id int,@name varchar(20)

select @id=id,@name=name from inserted

insert into table_b values(@id,@name)

end