Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need help to write a stored procedure / Dynamic SQL!! Expand / Collapse
Author
Message
Posted Saturday, December 05, 2009 2:18 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 21, 2013 11:03 AM
Points: 52, Visits: 187
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

Post #829445
Posted Saturday, December 05, 2009 7:06 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:29 PM
Points: 32,819, Visits: 14,965
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
Post #829476
Posted Saturday, December 05, 2009 9:15 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
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."
Post #829494
Posted Monday, December 07, 2009 3:14 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 21, 2013 11:03 AM
Points: 52, Visits: 187
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

Post #830312
Posted Monday, December 07, 2009 3:37 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
Good Job!

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #830321
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse