34,590
社区成员
发帖
与我相关
我的任务
分享
begin transaction
/*设定当前版本(版本递增)*/
declare @NewVersion_20101012_1 int;
set @NewVersion_20101012_1 = 10;
/*第一次更新,版本可能为空*/
if ( @NewVersion_20101012_1 > (select TableVer from config where ID = 1 ) or
@NewVersion_20101012_1 > ISNULL((select TableVer from config where ID = 1),0) )
begin
update config set TableVer = TableVer where ID = 1;
-- 加go的话编译出错
-- CREATE TRIGGER [dbo].[TriTable1Insert]
-- ON [dbo].[Nums]
-- FOR INSERT
-- AS
-- BEGIN
-- ...
-- END
--go
--CREATE TRIGGER [dbo].[TriTable1Insert2]
-- ON [dbo].[Nums]
-- FOR INSERT
-- AS
-- BEGIN
-- ...
-- END
/*更新版本*/
update config set TableVer = @NewVersion_20101012_1 where ID = 1;
end
/*如果有出错就回滚*/
if(@@error<>0)
begin
rollback transaction
end
/*提交*/
commit transaction
改用动态的
alter proc pppp
as
declare @s1 varchar(1001),@s2 varchar(1001)
set @s1=' CREATE TRIGGER [dbo].[tr1]
ON [dbo].tb1
FOR INSERT
AS
BEGIN
print ''a''
END'
set @s2='
CREATE TRIGGER [dbo].[tr2]
ON [dbo].tb2
FOR INSERT
AS
BEGIN
print ''b''
END'
exec(@s1)
exec(@s2)
print 'c'
go
exec pppp
select name from sysobjects where xtype='tr'
name
--------------------------------------------------------------------------------------------------------------------------------
tr1
tr2
(2 行受影响)
drop trigger tr1
drop trigger tr2
exec ('CREATE TRIGGER [dbo].[TriTable1Insert]....')