动态设置表结构方案（二）

动态设置表结构方案（二）

承接（一）

CREATE TRIGGER [dbo].[trgUpd] ON [dbo].[bigtable] AFTER UPDATEAS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for trigger here SELECT * INTO #inserted FROM INSERTED; SELECT * INTO #deleted FROM DELETED; DECLARE @sql VARCHAR(MAX) = ''; --删除旧纪录 SET @sql = 'DELETE FROM [bigtable.tmp] WHERE Id IN(SELECT Id FROM #deleted)'; EXEC(@sql); --插入新纪录 DECLARE @cols VARCHAR(MAX) = ''; SELECT @cols = @cols + ',' + C.name FROM sys.columns C WHERE C.[object_id] = OBJECT_ID('[bigtable]'); SET @cols = SUBSTRING(@cols,2,LEN(@cols) - 1); SET IDENTITY_INSERT [bigtable.tmp] ON; SET @sql = 'INSERT INTO [bigtable.tmp](' + @cols + ') SELECT ' + @cols + ' FROM #inserted'; EXEC(@sql); SET IDENTITY_INSERT [bigtable.tmp] OFF;ENDGOCREATE TRIGGER [dbo].[trgDel] ON [dbo].[bigtable] AFTER DELETEAS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for trigger here SELECT * INTO #deleted FROM DELETED; DECLARE @sql VARCHAR(MAX) = ''; --删除旧纪录 SET @sql = 'DELETE FROM [bigtable.tmp] WHERE Id IN(SELECT Id FROM #deleted)'; EXEC(@sql);ENDGO换表名：exec sp_rename '旧表名','新表名'

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。