一个简单触发器的结构
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