修改后触发器CREATE TRIGGER trig_test
ON test
FOR INSERT, UPDATE, DELETE
AS
IF EXISTS(SELECT * FROM inserted)
BEGIN
UPDATE allsearch
SET schttlname=i.[name]
from inserted i,allsearch x
WHERE x.schttlid=i.[id] AND x.schttlscid=i.recscid if @@rowcount>0
PRINT 'Update' INSERT INTO allsearch (schttlname, schttlid ,schttlscid, schttltcid, schttltcgrid)
select i.[name],i.id,i.recscid,s.sctcid,t.tcgrpid
from inserted i,subs_class s,top_class t
where s.scid=i.recscid and t.tcid=s.sctcid
and not exists (
select 1 from allsearch WHERE
schttlid=i.[id] AND schttlscid=i.recscid
) if @@rowcount>0
PRINT 'Insert'
END ELSE
BEGIN
PRINT 'Delete' delete allsearch
from deleted d,allsearch x
WHERE x.schttlid=d.[id] AND x.schttlscid=d.recscid
END
go
ON test
FOR INSERT, UPDATE, DELETE
AS
IF EXISTS(SELECT * FROM inserted)
BEGIN
UPDATE allsearch
SET schttlname=i.[name]
from inserted i,allsearch x
WHERE x.schttlid=i.[id] AND x.schttlscid=i.recscid if @@rowcount>0
PRINT 'Update' INSERT INTO allsearch (schttlname, schttlid ,schttlscid, schttltcid, schttltcgrid)
select i.[name],i.id,i.recscid,s.sctcid,t.tcgrpid
from inserted i,subs_class s,top_class t
where s.scid=i.recscid and t.tcid=s.sctcid
and not exists (
select 1 from allsearch WHERE
schttlid=i.[id] AND schttlscid=i.recscid
) if @@rowcount>0
PRINT 'Insert'
END ELSE
BEGIN
PRINT 'Delete' delete allsearch
from deleted d,allsearch x
WHERE x.schttlid=d.[id] AND x.schttlscid=d.recscid
END
go
1、如果使用
insert tabname(....) select ... from ... 或者
update ... 或者 delete ....
语句,一次可能会插入或者更新或者删除超过一条记录,楼主用变量取inserted表的记录,只能取到一条记录的值,这个缺陷是这个触发器的最大问题。2、触发器出错会自动回滚事务,所以不必在触发器中回滚。
3、触发器在执行正常的情况下会自动提交事务,所以不能在触发器中写提交事务的语句,否则执行的时候会出错,这是楼主触发器的另一大问题。
还有一个问题想请教,为何我用查询分析器向表test中增,删,改一条记录是可以的,
而用ADO访问数据库时,向表test中增,删,改一条记录就会出错,
不知未何,还请帮忙指点一下?
ON test
FOR INSERT, UPDATE, DELETE
AS
...-->
CREATE TRIGGER trig_test
ON test
FOR INSERT, UPDATE, DELETE
AS
set nocount on
...
Const CONNECTIONSTRING = "driver={sql server};server=LINWEI;UID=sa;PWD=123456; database=hztirn_t2" Set CON = Server.CreateObject("ADODB.Connection")
CON.Open(CONNECTIONSTRING) '创建事务
'CON.BeginTrans()
CON.Execute ("delete from test where name='name5' ")
'提交或回滚事务
If CON.Errors.Count>0 then
'CON.Errors.Clear()
'CON.RollbackTrans()
Response.Write"<script language='jscript'>alert('出现意外因素,导致操作失败!');</script>"
Else
'CON.CommitTrans()
Response.Write"<script language='jscript'>alert('栏目添加成功!');</script>"
End If
CON.Close()
Set CON = Nothing
我把代码改成
Dim CON, rst
Const CONNECTIONSTRING = "driver={sql server};server=LINWEI;UID=sa;PWD=123456; database=hztirn_t2" Set CON = Server.CreateObject("ADODB.Connection")
CON.Open(CONNECTIONSTRING) '创建事务
CON.BeginTrans()
CON.Execute ("delete from test where name='name5' ")
'提交或回滚事务
If CON.Errors.Count>0 then Response.Write"<script language='jscript'>alert('出现意外因素,导致操作失败!');</script>"
Else
Response.Write"<script language='jscript'>alert('栏目添加成功!');</script>"
End If
CON.CommitTrans()
CON.Close()
Set CON = Nothing
可以的,当然去掉上面的有关事务的创建和提交也是可以的,不知是为什么?