December 5, 2009 at 2:18 pm
Hi, I just started my career in SQL server , I have wriiten a trigger that inserts data into audit table z_luBarcodeSetting from the actual table luBarcodeSetting
But now my manager had asked me write a stored procedure that will script out SQL for triggers on all tables in the databse in the same format I have written the trigger.
Please Can some one help me out in writiing this stored procedure
CREATE TABLE [dbo].[luBarcodeSetting]
(
[BarCodeSettingID] [int] NOT NULL,
[LineOfBusinessID] [int] NOT NULL,
[PrintQuantity] [int] NULL,
[RecordingUserName] [varchar](20) NOT NULL,
[RecordingTime] [varchar](20) NOT NULL,
[Rowversion] [int] NOT NULL,
CONSTRAINT [PK_luBarcodeSetting] PRIMARY KEY CLUSTERED
(
[BarCodeSettingID] ASC
) ON [PRIMARY]
CREATE TABLE [dbo].[z_luBarcodeSetting](
[ActionDML] [varchar](1) NOT NULL,
[BarCodeSettingID] [int] NOT NULL,
[LineOfBusinessID] [int] NOT NULL,
[PrintQuantity] [int] NULL,
[RecordingUserName] [varchar](20) NOT NULL,
[RecordingTime] [varchar](20) NOT NULL,
[Rowversion] [int] NOT NULL,
CONSTRAINT [PK_z_luBarcodeSetting] PRIMARY KEY CLUSTERED
(
[BarCodeSettingID] ASC,
[Rowversion] ASC
) ON [PRIMARY]
Create TRIGGER [TR_UD_luBarcodeSetting] ON luBarcodeSetting
INSTEAD OF DELETE, UPDATE
AS
DECLARE @ErrorMessage VARCHAR(4000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
BEGIN TRY
INSERT INTO z_luBarcodeSetting
(ActionDML,BarcodeSettingID,LineOfBusinessID,PrintQuantity,RecordingUserName,RecordingTime,RowVersion)
SELECT CASE WHEN deleted.BarcodeSettingID IS NOT NULL AND inserted.BarcodeSettingID IS NOT NULL
THEN CASE WHEN deleted.ROWVERSION = -1 THEN 'M'
WHEN deleted.ROWVERSION = 0 THEN 'I'
ELSE 'U'
END
WHEN deleted.BarcodeSettingID IS NOT NULL AND inserted.BarcodeSettingID IS NULL
THEN 'D'
END as ActionDML,
deleted.BarcodeSettingID,deleted.LineOfBusinessID,deleted.PrintQuantity,deleted.RecordingUserName,deleted.RecordingTime,deleted.RowVersion
FROM deleted
LEFT JOIN luBarcodeSetting on luBarcodeSetting.BarcodeSettingID = deleted.BarcodeSettingID
LEFT JOIN inserted on luBarcodeSetting.BarcodeSettingID = inserted.BarcodeSettingID
BEGIN
IF (SELECT COUNT(*) FROM inserted) = 0
Delete luBarcodeSetting where BarcodeSettingID in (select BarcodeSettingID from deleted)
ELSE
UPDATE luBarcodeSetting set LineOfBusinessID = inserted.LineOfBusinessID
,PrintQuantity = inserted.PrintQuantity
,RecordingUserName = inserted.RecordingUserName
,RecordingTime = inserted.RecordingTime
,RowVersion=inserted.RowVersion
FROM luBarcodeSetting, inserted
WHERE luBarcodeSetting.BarcodeSettingID = inserted.BarcodeSettingID
END
END TRY
BEGIN CATCH
SELECT @ErrorMessage = 'TR_UD_luBarcodeSetting:' + ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
IF @@TRANCOUNT > 0
BEGIN
RAISERROR (@ErrorMessage, 16,1) with nowait
ROLLBACK TRAN
RETURN
END
END CATCH
December 5, 2009 at 7:06 pm
Here's a good hint for you:
December 5, 2009 at 9:15 pm
I do this quite a lot and it is really not very hard once you know how. I would recommend that you first try to work it out your self using the article that Steve has linked to, however, if you have more questions or need additional help, feel free to ask.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 7, 2009 at 3:14 pm
Thanks for the help steve and Barry !!!
well my task was to script the SQl for triggers in the same format what I written for a table initially . so I used cursors to do this and I did accomplished it
and here is the code
DECLARE Table_Trigger CURSOR
FOR
SELECT name TableName FROM sys.tables
DECLARE @table_name SYSNAME
DECLARE @Primary_key SYSNAME
DECLARE @Trigger VARCHAR(MAX)
-- Loop through All tables
OPEN Table_Trigger
FETCH NEXT FROM Table_Trigger INTO @table_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET NOCOUNT ON
SELECT @Primary_key = COL_NAME(ic.OBJECT_ID,ic.column_id)
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1 and i.object_id = Object_id(@table_name)
SET @Trigger = '-------------------'+@table_name+'
Create TRIGGER [TR_UD_'+@table_name+'] ON '+@table_name+
'
INSTEAD OF DELETE, UPDATE
AS
DECLARE @ErrorMessage VARCHAR(4000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
BEGIN TRY
INSERT INTO z_'+@table_name+'
([ActionDML],'
-- Loop through All Columns for a table
DECLARE Table_Column_Tri CURSOR
FOR
SELECT column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
DECLARE @column_name SYSNAME
DECLARE @Delete VARCHAR(MAX)
DECLARE @select VARCHAR(MAX)
DECLARE @Insert VARCHAR(MAX)
DECLARE @Pos int SET @Pos = 1
OPEN Table_Column_Tri
FETCH NEXT FROM Table_Column_Tri INTO @column_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@select IS NULL And @Pos = 1)
SET @select = QUOTENAME(@column_name)
ELSE
SET @select = @select + ' , ' +QUOTENAME(@column_name)
IF (@Delete IS NULL)
SET @Delete = 'Deleted.'+ QUOTENAME(@column_name)
ELSE
SET @Delete = @Delete + ' , ' + 'Deleted.'+ QUOTENAME(@column_name)
IF (@Insert IS NULL AND @column_name <> @Primary_key)
SET @insert = + QUOTENAME(@column_name)+ ' = inserted.'+QUOTENAME(@column_name)
ELSE
SET @insert = @insert + ' , ' + QUOTENAME(@column_name)+ ' = inserted.'+QUOTENAME(@column_name)
FETCH NEXT FROM Table_Column_Tri INTO @column_name
END
CLOSE Table_Column_Tri
DEALLOCATE Table_Column_Tri
SET @TRIGGER = @TRIGGER +@select+')
SELECT CASE WHEN deleted.'
+@Primary_key+' IS NOT NULL AND inserted.'+@Primary_key+' IS NOT NULL
THEN CASE WHEN deleted.ROWVERSION = -1 THEN '+''''+'M'+''''+'
WHEN deleted.ROWVERSION = 0 THEN '+''''+'I'+''''+'
ELSE '+''''+'U'+''''+'
END
WHEN deleted.'+@Primary_key+' IS NOT NULL AND inserted.'+@Primary_key+' IS NULL
THEN '+''''+'D'+''''+'
END as ActionDML,
'+@Delete+'
FROM deleted
LEFT JOIN '+@table_name+' on '+@table_name+'.'+@Primary_key+' = deleted.'+@Primary_key+'
LEFT JOIN inserted on '+@table_name+'.'+@Primary_key+' = inserted.'+@Primary_key+'
BEGIN
IF (SELECT COUNT(*) FROM inserted) = 0
Delete '+@table_name+' where '+@Primary_key+' in (select '+@Primary_key+' from deleted)
ELSE
UPDATE '+@table_name+' set '+@Insert+'
FROM '+@table_name+', inserted
WHERE '+@table_name+'.'+@Primary_key+' = inserted.'+@Primary_key+'
END
END TRY
BEGIN CATCH
SELECT @ErrorMessage = '+''''+'TR_UD_'+@table_name+':'+''''+'+ ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
IF @@TRANCOUNT > 0
BEGIN
RAISERROR (@ErrorMessage, 16,1) with nowait
ROLLBACK TRAN
RETURN
END
END CATCH'
---Print Trigger
SET @Delete = Null
SET @select = Null
SET @Insert = Null
PRINT @TRIGGER
PRINT ''
FETCH NEXT FROM Table_Trigger INTO @table_name
END
CLOSE Table_Trigger
DEALLOCATE Table_Trigger
December 7, 2009 at 3:37 pm
Good Job!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply