可以先看这个 create TRIGGER safety ON DATABASE FOR CREATE_TABLE AS declare @cmd nvarchar(max) set @cmd=EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(max)') print @cmd
能把语法帮我写出来吗,就写当A中有a1表创建了就转移到B中,怎么定时执行啊?
不错,这个修改一下换用EXEC就能达到目的了。
先USE 你的库名,把test.dbo的test改的你要复制的库名。
USE [mydb] GO CREATE TRIGGER [safety] ON DATABASE FOR CREATE_TABLE AS declare @cmd nvarchar(max) set @cmd=EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(max)') set @cmd= left(@cmd,charindex('table',@cmd)+5)+'[test].[dbo].' --把test替换成你复制后的库名 +parsename(substring(@cmd,charindex('table',@cmd)+5,charindex('(',@cmd)-(charindex('table',@cmd)+5)),1) +stuff(@cmd,1,charindex('(',@cmd)-1,'') exec(@cmd) GO
create TRIGGER safety
ON DATABASE
FOR CREATE_TABLE
AS
declare @cmd nvarchar(max)
set @cmd=EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(max)')
print @cmd
GO
CREATE TRIGGER [safety]
ON DATABASE
FOR CREATE_TABLE
AS
declare @cmd nvarchar(max)
set @cmd=EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(max)')
set @cmd= left(@cmd,charindex('table',@cmd)+5)+'[test].[dbo].' --把test替换成你复制后的库名
+parsename(substring(@cmd,charindex('table',@cmd)+5,charindex('(',@cmd)-(charindex('table',@cmd)+5)),1)
+stuff(@cmd,1,charindex('(',@cmd)-1,'')
exec(@cmd)
GO