MS SQL 惯用SQL语句（六）：创建、修改、删除触发器等操作sq

6、触发器操作：

--1、创建触发器
create trigger tesetTrigger --触发器名称
on employee  --在哪个表上定义触发器
for insert --触发器类型
as --程序语句
begin
	print 'tesetTrigger触发器被使用了';
end

--2、调用
insert into employee(empName,empAge,empPay,empJob)
values('李老师',25,2500,'高级讲师')


--3、指定触发器的触发顺序：只能指定第一个和最后一个，中间的随机触发
--先创建6个insert触发器
declare @count int;
declare @c int;
declare @triggerName varchar(50);
declare @sql varchar(100);
declare @msg varchar(50);
set @count = 6;
set @c = 1;
while(@c<=@count)
begin
	set @triggerName = 'tesetTrigger'+cast(@c as varchar(5));
	set @msg = @triggerName+'触发器被调用了';
	set @sql='create trigger '+@triggerName+' on employee for insert as begin print '''+@msg+''' end ';
	set @sql='create trigger '+@triggerName+' on employee for insert as begin print '''+@msg+''' end ';
	print @sql;
	exec(@sql);
	set @c = @c+1;
end

--执行insert语句查看调用顺序:tesetTrigger1、tesetTrigger2、tesetTrigger3、tesetTrigger4、tesetTrigger5、tesetTrigger6触发器依次被调用
insert into employee(empName,empAge,empPay,empJob)
values('何老师',35,7500,'高级讲师');

--调整触发器的执行过程：只能调整第一个和最后一个，将第一个定义为tesetTrigger6，最后一个定义为tesetTrigger1
exec sp_settriggerorder @triggername='tesetTrigger6',@order='first',@stmttype='insert';
exec sp_settriggerorder @triggername='tesetTrigger1',@order='last',@stmttype='insert';

--执行sql查看顺序：tesetTrigger6、tesetTrigger2、tesetTrigger3、tesetTrigger4、tesetTrigger5、tesetTrigger1触发器依次被调用
insert into employee(empName,empAge,empPay,empJob)
values('小兰老师',20,3500,'助教');

--取消触发器指定的执行顺序
exec sp_settriggerorder @triggername='tesetTrigger6',@order='none',@stmttype='insert';
exec sp_settriggerorder @triggername='tesetTrigger1',@order='none',@stmttype='insert';

--执行insert语句查看调用顺序:tesetTrigger1、tesetTrigger2、tesetTrigger3、tesetTrigger4、tesetTrigger5、tesetTrigger6触发器依次被调用
insert into employee(empName,empAge,empPay,empJob)
values('小金老师',24,3500,'助教');

--sp_settriggerorder触发器结构：
exec sp_settriggerorder @triggername='触发器名称',@order='first|last|none',@stmttype='触发器类型,insert|update|delete可选';

--4、instead of触发器
--先创建一个for的delete触发器
create trigger tesetDeleteTrigger
on employee 
for delete
as 
begin
	print 'tesetDeleteTrigger触发器被调用了';
end

--执行delete操作查看执行情况:数据删除且控制台打印“tesetDeleteTrigger触发器被调用了”
delete from employee where empId=15;

--再创建一个instead of的delete触发器
create trigger tesetInsteadOfDeleteTrigger
on employee 
instead of delete
as 
begin
	print '替代触发器tesetInsteadOfDeleteTrigger被调用了';
end

--执行delete操作查看执行情况:控制台打印“替代触发器tesetInsteadOfDeleteTrigger被调用了”但是数据没有删除
delete from employee where empId=16;

--比较：
--for触发器：直接操作数据，如添加、删除数据等，执行之后数据库中的数据发生变化
--instead of触发器：不操作数据，只是执行“触发器中规定的操作”，数据并不发生变化

--5、查看触发器
SELECT name, definition
FROM sys.sql_modules AS m
INNER JOIN sys.all_objects AS o ON m.object_id = o.object_id
WHERE o.[type] = 'tr'

--6、修改触发器
--修改与创建语法结构一致，就是将创建的create关键字修改成alter关键字即可,至于业务逻辑可以根据实际情况修改。
ALTER trigger tesetDeleteTrigger
on employee 
for insert,update,delete
as 
begin
	print 'tesetDeleteTrigger触发器修改后被调用了';
end

--7、删除触发器
drop trigger tesetDeleteTrigger;


--8、查看触发器的基本信息，包括名称、所有者、类型和创建时间
exec sp_help tesetDeleteTrigger;

--9、查看触发器的创建代码
exec sp_helptext tesetDeleteTrigger;
--注意：若是新建触发器时所有的代码都是1行（如tesetTrigger1）那么查到的代码就是一行的。若是如tesetDeleteTrigger有设置格式的，那么代码就是一行一行如新建时候显示。