Eshika (1/10/2012)

---

An overview of the trigger - There are two databases DB1-Transactional and DB2-Archive. There are two tables from each database, Tables1 & 2 from DB1 and Tables 3&4 from DB2.

The trigger involves both databases and 4 tables.

As stats column = A from table1 ;

1. updates the date column ;

2. The data will be moved to from TB1 -->Tb3 DB2

3. The corresponding data will be moved to from TB2 -->Tb4 DB2

And all the data from Tb1 & TB2 will be deleted after insert.

Table definition -

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [Schema1].[Table1](

[ROWID] [bigint] IDENTITY(1,1) NOT NULL,

[LID] [varchar](100) NOT NULL,

[NAME] [varchar](100) NULL,

[TOTALCOUNT] [int] NULL,

[PID] [int] NULL,

[CID] [int] NULL,

[OID] [int] NULL,

[ADATE] [datetime] NULL,

[RDATE] [datetime] NULL,

[CDATE] [date] NULL,

[STAT] [varchar](10) NULL,

[nvarchar](max) NULL,

[REACH] [varchar](4) NULL,

[SA] [varchar](4) NULL,

[RED] [char](1) NULL,

[DK] [varchar](4) NULL,

[DATEINDB] [datetime] NULL,

[MODDATE] [datetime] NULL,

[TID] [varchar](100) NULL,

CONSTRAINT [PK_Table1] PRIMARY KEY NONCLUSTERED

(

[ROWID] ASC,

[LID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

ALTER TABLE [Schema1].[Table1] ADD CONSTRAINT [DF_Table1_STAT] DEFAULT ('A') FOR [STAT]

GO

ALTER TABLE [Schema1].[Table1] ADD CONSTRAINT [DF_Table1_DATEINDB] DEFAULT (getdate()) FOR [DATEINDB]

GO

Trigger Statement -

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER TRIGGER [Schema1].[Table1_STAT]

ON [Schema1].[Table1]

FOR UPDATE ,INSERT , DELETE

AS

BEGIN

IF NOT UPDATE(MODDATE)

UPDATE [Schema1].[Table1]

SET MODDATE=GETDATE()

WHERE LID IN (SELECT LID FROM inserted WHERE STATUS in ('C','X'))

---Insert the C or X lead sheet to arch

INSERT INTO [DB2].[dbo].[ArchTable2]

([ROWID] ,[LID],[NAME],[TOTALCOUNT],[PID],[CID],[OID],[ADATE],[RDATE]

[CDATE],[STAT] , ,[REACH] ,[SA] ,[RED], [DK] ,[DATEINDB],[MODDATE],[TID])

SELECT [ROWID] ,[LID],[NAME],[TOTALCOUNT],[PID],[CID],[OID],[ADATE],[RDATE]

[CDATE],[STAT] , ,[REACH] ,[SA] ,[RED], [DK] ,[DATEINDB],[MODDATE],[TID]

FROM inserted

WHERE LID IN (SELECT LID FROM inserted WHERE STAT in ('C','X'))

-- Insert the closed or disabled lead sheet associated leads to arch

INSERT INTO [DB2].[dbo].[ARCHTable3]

(80 Columns)

SELECT 80 columns

FROM [DB1].[Schema1].[Table2] A INNER JOIN inserted I

ON A. [LID] = I.LID

WHERE I.STAT in ('C','X')

-- Delete all the closed or disabled lead sheets and leads after archiving

DELETE [DB1].[Schema1].[Table2] WHERE LID IN (SELECT LID FROM inserted WHERE STAT in ('C','X'))

DELETE [DB1].[Schema1].[Table1] WHERE LID IN (SELECT LID FROM inserted WHERE STATUS in ('C','X'))

END