触发器SQL1

--触发器：执行一个操作会自动 触发另外 一些 操作
--触发器是针对有可能修改表数据的操作而言：增加删除和修改，对查询没用。
--触发器是针对于某一个表执行什么样的操作进而触发触发器
--语法：
--create trigger tr_表.操作
--after/for:操作之后触发
--instead of：不执行当前操作，而使用触发器中的操作进行替代
--on 操作的表 after/for|instead of insert|delete|update
--as
-- 逻辑语句
--go
if exists(select * from sysobjects where name=‘tr_classes_insert‘)
drop trigger tr_classes_insert
go
create trigger tr_classes_insert
on classes instead of insert
as
select * from student
select * from result
delete from classes where classid=21
go

insert into Classes values(‘恭恭敬敬‘)

--关注inserted deleted表
select * from inserted
select * from deleted

if exists(select * from sysobjects where name=‘tr_classes_insert‘)
drop trigger tr_classes_insert
go
create trigger tr_classes_insert
on classes after insert
as
print ‘inserted:是操作之后的表，它是存储操作之后的数据,数据与原始的物理表没有关系,只与当前这次操作所影响的数据有关‘
select * from inserted
print ‘deleted:是操作之前的表，它是存储操作之前的数据,只存储与当前操作相关的数据‘
select * from deleted
go
insert into Classes values(‘gg‘)

if exists(select * from sysobjects where name=‘tr_classes_update‘)
drop trigger tr_classes_update
go
create trigger tr_classes_update
on classes after update
as
print ‘inserted:是操作之后的表，它是存储操作之后的数据,数据与原始的物理表没有关系,只与当前这次操作所影响的数据有关‘
select * from inserted
print ‘deleted:是操作之前的表，它是存储操作之前的数据,只存储与当前操作相关的数据‘
select * from deleted
go
update Classes set classname=classname+‘123‘ where ClassId>18

if exists(select * from sysobjects where name=‘tr_classes_delete‘)
drop trigger tr_classes_delete
go
create trigger tr_classes_delete
on classes after delete
as
print ‘inserted:是操作之后的表，它是存储操作之后的数据,数据与原始的物理表没有关系,只与当前这次操作所影响的数据有关‘
select * from inserted
print ‘deleted:是操作之前的表，它是存储操作之前的数据,只存储与当前操作相关的数据‘
select * from deleted
go

delete from Classes where ClassId>18

if exists(select * from sysobjects where name=‘tr_classes_insert‘)
drop trigger tr_classes_insert
go
create trigger tr_classes_insert
on classes after insert
as
declare @cid int,@cname nvarchar(50)
select @cid=classid,@cname=classname from inserted
--判断当前插入的班级名称是否在原始的物理中存在过
if((select count(*) from classes where classname=@cname)>1)
begin
print ‘已经存在‘
delete from classes where classid=@cid
end
else
print ‘ok‘
go
insert into Classes values(‘ggg‘)