CREATE TRIGGER [insertQXBMGN] ON [dbo].[QXBMGN]
FOR INSERT
AS
DECLARE
@bmbh varchar(30),
@gnmc varchar(30)
select @bmbh=部门编号,@gnmc=功能名称 from inserted
insert into QXBMAN(部门编号,窗体名,按钮名,按钮名称,是否可用)
(select @bmbh,QXCTAN.窗体名,QXCTAN.按钮名,QXCTAN.按钮名称,0 from QXGNCT,QXCTAN where QXGNCT.功能名称=@gnmc and QXGNCT.窗体名=QXCTAN.窗体名)
我想把上面的触发器改成存储过程,要怎样改.
因为上面触发器用DELPHI程序好象会出现重复记录的错误,可是我在SQLSERVER手动加,就可以.不知道是什么原因.所以我想改成存储过程,但不知要怎样改,大家帮忙一下.谢谢!
FOR INSERT
AS
DECLARE
@bmbh varchar(30),
@gnmc varchar(30)
select @bmbh=部门编号,@gnmc=功能名称 from inserted
insert into QXBMAN(部门编号,窗体名,按钮名,按钮名称,是否可用)
(select @bmbh,QXCTAN.窗体名,QXCTAN.按钮名,QXCTAN.按钮名称,0 from QXGNCT,QXCTAN where QXGNCT.功能名称=@gnmc and QXGNCT.窗体名=QXCTAN.窗体名)
我想把上面的触发器改成存储过程,要怎样改.
因为上面触发器用DELPHI程序好象会出现重复记录的错误,可是我在SQLSERVER手动加,就可以.不知道是什么原因.所以我想改成存储过程,但不知要怎样改,大家帮忙一下.谢谢!
@bmbh varchar(30),
@gnmc varchar(30)
as
select @bmbh=部门编号,@gnmc=功能名称 from inserted
insert QXBMAN (部门编号,窗体名,按钮名,按钮名称,是否可用)
select @bmbh,QXCTAN.窗体名,QXCTAN.按钮名,QXCTAN.按钮名称,0 from QXGNCT,QXCTAN where QXGNCT.功能名称=@gnmc and QXGNCT.窗体名=QXCTAN.窗体名)
UsPwoMainForm.QXBMGN.Connection.BeginTrans;
showmessage('1');
for i:=0 to UsPwoMainFORM.QXBMGN.RecordCount-1 do
begin
QXBMANADSP.Parameters.ParamByName('@bmbh').Value:=qxbmbh;
QXBMANADSP.Parameters.ParamByName('@GNMC').Value:=UsPwoMainFORM.QXBMGN['功能名称'];
QXBMANADSP.ExecProc;
showmessage('2');
UsPwoMainFORM.QXBMGN.NEXT;
end;
showmessage('3');
UsPwoMainFORM.QXBMGN.UpdateBatch;
showmessage('5');
UsPwoMainForm.QXBMGN.Connection.CommitTrans;
showmessage('设置权限成功!');
close;
except
UsPwoMainForm.QXBMGN.Connection.RollbackTrans;
showmessage('设置不成功,请重新设置!');
UsPwoMainFORM.QXBMGN.CancelBatch;
end;
当UsPwoMainFORM.QXBMGN.UpdateBatch;提交失败,产生回滚,会不会把QXBMANADSP.ExecProc;执行完的都回滚.如果不会要怎样改