March 23, 2012 at 2:29 am
Hi all,
I had a table named dbo.TableA and it contains 2 columns and another copy of this table in Archive.TableA.
Records will be inserted into Archive.TableA using triggers in dbo.TableA.
Suppose I add or remove a column in dbo.TableA it should add in Archive.TableA. This I had done using database triggers.
i need to alter that triggers in dbo.TableA.
USE [Demo]
GO
/****** Object: Table [dbo].[TableA] Script Date: 03/23/2012 13:37:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TableA](
[AId] [int] IDENTITY(1,10) NOT NULL,
[BId] [int] NOT NULL,
[test] [int] NULL
) ON [PRIMARY]
GO
USE [Demo]
GO
/****** Object: Trigger [dbo].[trTableA] Script Date: 03/23/2012 13:37:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[trTableA]
ON [dbo].[TableA]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @inserted int = (SELECT COUNT(1) FROM inserted);
DECLARE @deleted int = (SELECT COUNT(1) FROM deleted);
DECLARE @auditTime datetimeoffset(7) = sysdatetimeoffset()
IF @inserted > 0 AND @deleted = 0
BEGIN
INSERT Archive.TableA(AuditType, AuditDate, AId, BId)
SELECT 'Inserted', GETDATE(), AId, BId
FROM inserted;
END
IF @inserted > 0 AND @deleted > 0
BEGIN
INSERT Archive.TableA(AuditType, AuditDate, AId, BId)
SELECT 'Update (before)', GETDATE(), AId, BId
FROM deleted;
INSERT Archive.TableA(AuditType, AuditDate, AId, BId)
SELECT 'Update (after)', GETDATE(), AId, BId
FROM inserted;
END
IF @inserted = 0 AND @deleted > 0
BEGIN
INSERT Archive.TableA(AuditType, AuditDate, AId, BId)
SELECT 'Delete', GETDATE(), AId, BId
FROM deleted;
END
END;
GO
USE [Demo]
GO
/****** Object: Table [Archive].[TableA] Script Date: 03/23/2012 13:38:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Archive].[TableA](
[AuditType] [nvarchar](15) NOT NULL,
[AuditDate] [datetime] NOT NULL,
[AId] [int] NOT NULL,
[BId] [int] NOT NULL
) ON [PRIMARY]
GO
alter TRIGGER [trArchive_ddl_event] ON DATABASE FOR ALTER_TABLE, DROP_TABLE, ALTER_INDEX, DROP_INDEX
AS
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
SET QUOTED_IDENTIFIER ON;
SET NOCOUNT ON;
IF EXISTS(SELECT 1 FROM Archive.change_tables WHERE name = CONVERT(sysname, EventData().query('data(//ObjectName)')))
BEGIN
INSERT INTO Archive.ddl_history (name, ddl_command, ddl_user, ddl_time)
SELECT CONVERT(sysname, EventData().query('data(//ObjectName)'))
,CONVERT(sysname, EventData().query('data(//TSQLCommand)'))
,CONVERT(NVARCHAR(100), SYSTEM_USER)
,GETDATE();
Declare @SQLcmd nvarchar(max)
select @SQLcmd=CONVERT(sysname, EventData().query('data(//TSQLCommand)'))
set @SQLcmd=Replace(@SQLcmd,PARSENAME(REPLACE(@SQLcmd, 'table ' , '.'), 2),' Archive');
exec (@SQLcmd)
END;
Thanks in Advance.
Regards
Guru
March 23, 2012 at 2:41 am
its going to have to be a manual process of altering the trigger or try and write some dynamic SQL which loads the trigger deffinition from the system tables and adds in the new column and the logic it requires into the trigger or subsequently removes the column and the column logic should it be dropped
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply