Update takes too long and freezes the server

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,
[NOTE] [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] ,[NOTE] ,[REACH] ,[SA] ,[RED], [DK] , [DATEINDB],[MODDATE],[TID])
SELECT [ROWID] ,[LID],[NAME],[TOTALCOUNT],[PID],[CID],[OID],[ADATE],[RDATE]
[CDATE],[STAT] ,[NOTE] ,[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