SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need help to write a stored procedure / Dynamic SQL!!


Need help to write a stored procedure / Dynamic SQL!!

Author
Message
shashi_1409
shashi_1409
SSC-Enthusiastic
SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)

Group: General Forum Members
Points: 129 Visits: 216
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


Steve Jones
Steve Jones
SSC Guru
SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)

Group: Administrators
Points: 82575 Visits: 19223
Here's a good hint for you:

http://www.sqlservercentral.com/scripts/Triggers/63064/

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
RBarryYoung
RBarryYoung
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19230 Visits: 9518
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.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
shashi_1409
shashi_1409
SSC-Enthusiastic
SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)

Group: General Forum Members
Points: 129 Visits: 216
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


RBarryYoung
RBarryYoung
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19230 Visits: 9518
Good Job!

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search