触发器

---------------------------
after(触发器)  是在提交后触发
AFTER
指定触发器只有在触发 SQL 语句中指定的所有操作都已成功执行后才激发。
所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。
如果仅指定 FOR 关键字，则 AFTER 是默认设置。
不能在视图上定义 AFTER 触发器
for 默认为after触发器，是在插入成功后才引发了触发器里面的sql，
-------------------------------------------------------------------------------------------------------------
instead of(触发器)   是在提交前触发 
INSTEAD OF
指定执行触发器而不是执行触发 SQL 语句，从而替代触发语句的操作。
INSTEAD OF 是替代触发器
-------------------------------------------------------------------------------------------------------------
AFTER 与INSTEAD OF的区别
Eg:
年月    建设单位   施工单位     合同编号
200501    A01        B01         200501A01B01- 00001
200501    A01        B01         200501A01B01- 00002
200501    A02        B01         200501A02B01- 00001
200501    A02        B01         200501A02B01- 00002
200502    A01        B02         200502A01B02- 00001
200502    A01        B02         200502A01B02- 00002

要求合同编号自动生成的语句
求:每次和成批全部自动生成语句
---------------------------
drop table A
drop Function T_Fun
drop TRIGGER TEMP_SC

create table A
(
   年月 varchar(10),
   建设单位 varchar(10), 
   施工单位 varchar(10),
   合同编号 varchar(100)
)

create Function T_Fun(@t1 varchar(20),@t2 varchar(20),@t3 varchar(20))
returns varchar(100)
begin
   declare @T_ID varchar(100)
   select @T_ID=@t1+@t2+@t3 + '-' + right(cast(100000+isnull(max(cast(right(合同编号,5) as int)),0)+1 as varchar),5) from A where 年月+建设单位+施工单位=@t1+@t2+@t3
   return (@T_ID)
end

CREATE TRIGGER TEMP_SC on A
INSTEAD OF Insert
as
   insert into A select A.年月,A.建设单位,A.施工单位,dbo.T_Fun(A.年月,A.建设单位,A.施工单位) from inserted A

--注意比较 INSTEAD OF 与  AFTER
delete from A
drop TRIGGER TEMP_SC
CREATE TRIGGER TEMP_SC on A
AFTER Insert
as
   insert into A select A.年月,A.建设单位,A.施工单位,dbo.T_Fun(A.年月,A.建设单位,A.施工单位) from inserted A

--Test
insert A select '200501','A01','B01',''
select * from A
-------------------------------------------------------------------------------------------------------------
其它
1.触发器的内容和存储过程一样,都是将一些数据处理命令放在一齐执行
  不同的是:存储过程中调用它时才执行,触发器在对表中的数据进行操作时被自动调用

2.在何时(那种操作时)会执行触发器
这个由for语句决定.
for insert 代表插入数据时
for update 代表更新数据时
for delete 代表删除数据时
insert update delete可以组合.例如:for insert,update 表示新增或更新数据时

3.得到操作影响的数据,由inserted和deleted这两个逻辑表得到,结果与原表完全一样
inserted  新增的数据,或更新操作时,更新后的数据
deleted   删除的数据,或更新操作时,更新前的数据
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
--创建表
create table tb(id int,name varchar(20))

--为这个表创建触发器,作用是检查新增数据时,名称是否重复,如果重复,取消插入:
create trigger t_chk on tb
for insert
as
if exists(select 1 from tb where name in(select name from inserted))
  rollback tran
go

--插入数据
insert into tb values(1,'张三')  --可以插入成功
insert into tb values(2,'张三')  --因为名称重复,插入会被触发器取消

--显示结果
select * from tb

 

 

 

 

-------------------------------------------------------------------------------------------------------------
drop TRIGGER TEMP_SC
--AFTER触发器
CREATE TRIGGER TEMP_SC ON T
AFTER Insert
as
print '成功'

insert T(T1,T2) values('F',NUll)

--INSTEAD OF触发器
CREATE TRIGGER TEMP_SC ON T
INSTEAD OF Insert
as
print '成功'
go
insert T(T1,T2) values('G','')

--$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
/*
CREATE TRIGGER 名1 ON dbo.tabMain
FOR INSERT
AS
insert db2..tabSlave select * from inserted
go

CREATE TRIGGER 名2 ON dbo.tabMain
FOR update
AS
update db2..tabSlave set 列=aa.列 from inserted aa where aa.编号=db2..tabSlave.编号
go

CREATE TRIGGER 名3 ON dbo.tabMain
FOR delete
AS
delete db2..tabSlave where 编号 in (select 编号 from deleted)
go
*/
--单行
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T1]
GO

CREATE TABLE [dbo].[T1] (
 [TID] [int] NOT NULL ,
 [X1] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [X2] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
--ID不是自动增长的情况
--插入
select * into T2 from t1

CREATE TRIGGER TEMP_SC ON T1
AFTER Insert
as
insert into t2(Tid,X1,X2) select * from Inserted
--------

insert T1(TID,X1,X2) values(1,'Q','199')
select * from t1 where X2='199'
select * from t2 where X2='199'
--修改
CREATE TRIGGER TEMP_SC ON T1
AFTER Update
as
update T2 set X1=T1.X1,X2=T1.X2 from inserted t1 where T1.TID=T2.TID
-------------

update T1 set X2='444' where X1='Q'
select * from t1 where tid=1
select * from t2 where tid=1
--删除
CREATE TRIGGER TEMP_SC ON T1
AFTER delete
as
delete T2 where TID in (select TID from deleted)
---------

DELETE T1 where TID=1
select * from t1 where X2='111'
select * from t2 where X2='111'

--ID是自动增长的情况
CREATE TABLE [dbo].[T1] (
 [TID] [int] IDENTITY (1, 1) NOT NULL ,
 [X1] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [X2] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]

--插入
select * into T2 from t1

CREATE TRIGGER TEMP_SC ON T1
AFTER Insert
as
declare @Temp_TID varchar(20)
select @Temp_TID=TID from inserted
insert into t2(X1,X2) select X1,X2 from T1 where T1.TID=@Temp_TID
------------

insert T1(X1,X2) values('Q1','199')
insert T1(X1,X2) values('Q2','199')
insert T1(X1,X2) values('Q3','199')
select * from t1
select * from t2

select * from t1 where X2='199'
select * from t2 where X2='199'
--修改
CREATE TRIGGER TEMP_SC ON T1
AFTER Update
as
declare @Temp_TID varchar(20)
select @Temp_TID=TID from inserted
update T2 set X1=T1.X1,X2=T1.X2 from  T1,T2 where T1.TID=T2.TID and T1.TID=@Temp_TID
-------------

update T1 set X2='444' where X1='Q'
select * from t1 where tid=1
select * from t2 where tid=1
--删除
CREATE TRIGGER TEMP_SC ON T1
AFTER delete
as
declare @Temp_TID varchar(20)
select @Temp_TID=TID from deleted
delete T2 where TID in (@Temp_TID)
----------

DELETE T1 where TID=1
select * from t1 where tid=1
select * from t2 where tid=1