create trigger [trigger_name] on [table] --在哪张表建立触发器 for insert --表示在这张表执行insert插入操作的时候自动调用下面内容 as --假如插入后返回结果如下 if exists(select * from inserted)--如果存在插入列 print 'success' --输出返回成功 else print 'wrong' --不存在输出失败
for 后面可以跟的关键字有 insert delete update for可以用before after 来替换 分别表示实行以上操作之前之后来执行下面的SQL语句! 谢谢。 详细内容还是自己看看资料哈~ thank you
触发器的语句格式 --------------------------- 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 goCREATE TRIGGER 名2 ON dbo.tabMain FOR update AS update db2..tabSlave set 列=aa.列 from inserted aa where aa.编号=db2..tabSlave.编号 goCREATE 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] GOCREATE 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 t1CREATE 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 t1CREATE 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
for insert --表示在这张表执行insert插入操作的时候自动调用下面内容
as
--假如插入后返回结果如下
if exists(select * from inserted)--如果存在插入列
print 'success' --输出返回成功
else
print 'wrong' --不存在输出失败
for可以用before after 来替换 分别表示实行以上操作之前之后来执行下面的SQL语句!
谢谢。
详细内容还是自己看看资料哈~
thank you
---------------------------
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
goCREATE TRIGGER 名2 ON dbo.tabMain
FOR update
AS
update db2..tabSlave set 列=aa.列 from inserted aa where aa.编号=db2..tabSlave.编号
goCREATE 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]
GOCREATE 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 t1CREATE 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 t1CREATE 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