Friday 11 July 2014

TRIGGER

USE [DBname]
GO
/****** Object: Trigger [dbo].[UpdateOldFQNs] Script Date: 04/05/2010 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT 1
FROM sysobjects
WHERE name = 'trUpdateOldFQNs'
AND TYPE = 'TR')
BEGIN
DROP TRIGGER dbo.trUpdateOldFQNs
PRINT 'Existing trigger "dbo.trUpdateOldFQNs" dropped succesfully.'
END
GO
-- =============================================
-- Author :
-- Create date :
-- Description :
--
--
--
-- =============================================
CREATE TRIGGER [dbo].[trUpdateOldFQNs]
ON [dbo].[TblEmployeeInfo]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
-- get before and after values
DECLARE @OldFQN AS VARCHAR(100)
DECLARE @NewFQN AS VARCHAR(100)
SELECT @OldFQN = FQN FROM DELETED
SELECT @NewFQN = FQN FROM INSERTED
BEGIN TRAN UpdateOldFQNs
-- Insert statements for trigger here
IF @OldFQN @NewFQN
BEGIN
UPDATE [dbo].tbl SET OriginatorID = @NewFQN WHERE OriginatorID = @OldFQN
END
COMMIT TRAN UpdateOldFQNs
END TRY
BEGIN CATCH
ROLLBACK TRAN UpdateOldFQNs
RAISERROR ('Error while udpating the values in tables tbl.',16,1)
END CATCH
END
GO
IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME = N'trUpdateOldFQNs' AND TYPE = N'TR')
PRINT 'Trigger "dbo.trUpdateOldFQNs" has been created successfully'
ELSE
PRINT 'Failed to create trigger "dbo.trUpdateOldFQNs"'
GO