if exists(select 1 from inserted) if exists(select 1 from deleted) print '修改' else print '新增' else print '删除'
sql中目前不支持自定义的全局变量/常量
另外,可以直接定义某种类型的触发器,这样就不用判断了 create triggr .... for insert --定义新增触发器create trigger ... for update --定义修改触发器create trigger ... for delete --定义删除触发器
另外一个问题: 我做了个实验,建了个触发器;CREATE TRIGGER InsertRecord ON [dbo].[tblAbc] FOR INSERT AS declare @StrSql as varchar(50) set @StrSql ='10'Exec ('select * from inserted where fldID=' + @StrSql)然后,我试着为tblAbc增加一条记录,但是sqlserver报错,说 对象名inserted无效! 于是,我干脆再简单一点,变成这样: CREATE TRIGGER InsertRecord ON [dbo].[tblAbc] FOR INSERT ASExec ('select * from inserted')还是报错 对象名inserted无效!最后,我变成这样: CREATE TRIGGER InsertRecord ON [dbo].[tblAbc] FOR INSERT AS select * from inserted新增记录成功了!这是为什么?难道说用了Exec 语句,sqlserver就不认得inserted表了? 请大虾指教,谢谢!
原几种触发器可以同时CREATE 的呀?原来都是分别CREATE 的,看来是费劲了……
研究一下COLUMNS_UPDATED() 的用法吧。
inserted是逻辑表,只在触发器中存在 而exec是开启了一个新过程,所以当然认不了inserted 改临时表就可以了 CREATE TRIGGER InsertRecord ON [dbo].[tblAbc] FOR INSERT AS select * into #t from inserted Exec ('select * from #t')
CREATE TRIGGER ... on ... FOR insert,update,delete AS if exists(select 1 from inserted) and not exists(select 1 from deleted) BEGIN ...此处写insert时的代码... END if exists(select 1 from inserted) and exists(select 1 from deleted) BEGIN ...此处写update时的代码... END if not exists(select 1 from inserted) and exists(select 1 from deleted) BEGIN ...此处写delete时的代码... END
if exists(select 1 from deleted)
print '修改'
else
print '新增'
else
print '删除'
create triggr ....
for insert --定义新增触发器create trigger ...
for update --定义修改触发器create trigger ...
for delete --定义删除触发器
我做了个实验,建了个触发器;CREATE TRIGGER InsertRecord ON [dbo].[tblAbc]
FOR INSERT
AS
declare @StrSql as varchar(50)
set @StrSql ='10'Exec ('select * from inserted where fldID=' + @StrSql)然后,我试着为tblAbc增加一条记录,但是sqlserver报错,说 对象名inserted无效!
于是,我干脆再简单一点,变成这样:
CREATE TRIGGER InsertRecord ON [dbo].[tblAbc]
FOR INSERT
ASExec ('select * from inserted')还是报错 对象名inserted无效!最后,我变成这样:
CREATE TRIGGER InsertRecord ON [dbo].[tblAbc]
FOR INSERT
AS
select * from inserted新增记录成功了!这是为什么?难道说用了Exec 语句,sqlserver就不认得inserted表了?
请大虾指教,谢谢!
而exec是开启了一个新过程,所以当然认不了inserted
改临时表就可以了
CREATE TRIGGER InsertRecord ON [dbo].[tblAbc]
FOR INSERT
AS
select * into #t from inserted
Exec ('select * from #t')
FOR insert,update,delete
AS
if exists(select 1 from inserted) and not exists(select 1 from deleted)
BEGIN
...此处写insert时的代码...
END
if exists(select 1 from inserted) and exists(select 1 from deleted)
BEGIN
...此处写update时的代码...
END
if not exists(select 1 from inserted) and exists(select 1 from deleted)
BEGIN
...此处写delete时的代码...
END