Technical Article

Auditing Solution - Generate Triggers automatically

,

The procedure looks at the table you want to be audited and writes the INS, UPD, and DEL triggers automatically required for the auditing to work. The auditing requires a table called Progress to hold the audit information in so have provided the DDL for this as well. Typically you would write a cursor to repeatedly call the procedure for all your tables you want audited. You can rerun at anytime so as new columns get added the triggers pick them up.

Typical call:-

utl_AuditTriggerTable 'YourTable', 'Y', 'Y', 'Y', 0

--First the DDL which creates the table where the audits are stored. I call this Progress instead of Audit becuase in our database Progress is used to log all kinds of other events
CREATE TABLE [dbo].[Progress] (
	[nProgressID] [int] IDENTITY (1, 1) NOT NULL ,
	[cProgressType] [char] (10) NOT NULL ,
	[nProgress] [int] NULL ,
	[cProgress] [varchar] (255) NULL ,
	[cSource] [char] (30) NULL ,
	[cWho]	[CHAR] (10) NULL, 
	[dtLastUpd] [datetime] NULL 
)
GO

CREATE TABLE [dbo].[ProgressTypes] (
	[cProgressType] [char] (10) NOT NULL ,
	[cProgressTypeDesc] [varchar] (255) NULL ,
)
GO

ALTER TABLE [dbo].[Progress] WITH NOCHECK ADD 
	CONSTRAINT [PK_PROGRESS] PRIMARY KEY  CLUSTERED 
	(
		[nProgressID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[ProgressTypes] WITH NOCHECK ADD 
	CONSTRAINT [PK_PROGRESSTYPES] PRIMARY KEY  CLUSTERED 
	(
		[cProgressType]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [FK_PROT_PRO] ON [dbo].[Progress]([cProgressType]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_PRO_cSource] ON [dbo].[Progress]([cSource]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_PRO_dtLastUpd] ON [dbo].[Progress]([dtLastUpd]) ON [PRIMARY]
GO


ALTER TABLE [dbo].[Progress] ADD 
	CONSTRAINT [FKC_PROT_PRO] FOREIGN KEY 
	(
		[cProgressType]
	) REFERENCES [dbo].[ProgressTypes] (
		[cProgressType]
	)
GO
INSERT progresstypes VALUES ('AUDIT_DEL','Table Audit on DELETES')
INSERT progresstypes VALUES ('AUDIT_INS','Table Audit on INSERTS')
INSERT progresstypes VALUES ('AUDIT_UPD','Table Audit on UPDATES')
GO

--Now the two procs...


SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO


CREATE PROCEDURE dbo.utl_AuditTrigger_Create
	@asTableName		CHAR(30)
	,@asTriggerType	CHAR(6)
	,@asBody1		VARCHAR(8000)
	,@asBody2		VARCHAR(8000)
	,@asBody3		VARCHAR(8000)
	,@asBody4		VARCHAR(8000)
	,@asUpdateJoin		VARCHAR(1000)
	,@anError		INT OUTPUT
	AS
/********************************************************************************************************************************************************
Purpose: 	Actually creates the triggers

Amendment Log
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Date 		Who			Comment
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
29/11/00	JHAYNE		Initial Version
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
********************************************************************************************************************************************************/
SET NOCOUNT ON

DECLARE
	@lsSource		CHAR(30)
	,@lsError		VARCHAR(255)
	,@lsDropTrigger	VARCHAR(255)
	,@lsHeader		VARCHAR(2000)
	,@lsFooter		VARCHAR(1000)
	,@lsBody1		VARCHAR(4000)
	,@lsBody1a		VARCHAR(4000)
	,@lsBody2		VARCHAR(4000)
	,@lsBody2a		VARCHAR(4000)
	,@lsBody3		VARCHAR(4000)
	,@lsBody3a		VARCHAR(4000)
	,@lsBody4		VARCHAR(4000)
	,@lsBody4a		VARCHAR(4000)
	,@lsWhere		VARCHAR(50)
	
--Initialise
SELECT @lsSource = OBJECT_NAME(@@PROCID)
SELECT @lsWhere = CHAR(13)
	
--Drop Trigger
SELECT
	@lsDropTrigger = 
		+ 'IF EXISTS (SELECT * FROM SysObjects WHERE Type = ''TR'' AND Name = ''trg_Audit_' 
		+ RTRIM(@asTableName)
		+ '_'
		+ LEFT(@asTriggerType, 3)
		+ ''') DROP TRIGGER trg_Audit_'
		+ RTRIM(@asTableName)
		+ '_' 
		+ LEFT(@asTriggerType, 3)
EXEC(@lsDropTrigger)
	
SELECT 
	@lsHeader = 
		+ 'CREATE TRIGGER dbo.trg_Audit_'
		+ RTRIM(@asTableName)
		+ '_' 
		+ LEFT(@asTriggerType, 3)
		+ ' ON ' 
		+ RTRIM(@asTableName) + CHAR(13)
		+ 'FOR ' 
		+ @asTriggerType + CHAR(13)
		+ 'AS' + CHAR(13) + CHAR(10) + CHAR(13)
		+ '--Generated programmatically. Please do not edit' + CHAR(13) + CHAR(10) + CHAR(13)
		+ 'INSERT Progress' + CHAR(13)
		+ CHAR(9) + '(' + CHAR(13)
		+ CHAR(9) + 'cProgressType' + CHAR(13)
		+ CHAR(9) + ',cSource' + CHAR(13)
		+ CHAR(9) + ',cWho' + CHAR(13)
		+ CHAR(9) + ',dtLastUpd' + CHAR(13)
		+ CHAR(9) + ',cProgress' + CHAR(13)
		+ CHAR(9) + ',nProgress' + CHAR(13)
		+ CHAR(9) + ')' + CHAR(13)
		+ 'SELECT' + CHAR(13)
		+ CHAR(9) + '''AUDIT_' + LEFT(@asTriggerType, 3) + '''' + CHAR(13)
		+ CHAR(9) + ',''' + RTRIM(@asTableName) + '''' + CHAR(13)		
		+ CHAR(9) + ',SUBSTRING(SUSER_SNAME(), CHARINDEX(''\'', SUSER_SNAME()) + 1, 10)' + CHAR(13)
		+ CHAR(9) + ',GETDATE()' + CHAR(13)
		+ CHAR(9) + ',LEFT(' + CHAR(13)

--Set Body
SELECT 
	@lsBody1 = LEFT(@asBody1, 4000)
	,@lsBody1a = SUBSTRING(@asBody1, 4001, 4000)
	,@lsBody2 = LEFT(@asBody2, 4000)
	,@lsBody2a = SUBSTRING(@asBody2, 4001, 4000)
	,@lsBody3 = LEFT(@asBody3, 4000)
	,@lsBody3a = SUBSTRING(@asBody3, 4001, 4000)
	,@lsBody4 = LEFT(@asBody4, 4000)
	,@lsBody4a = SUBSTRING(@asBody4, 4001, 4000)

--Set Join for UPDATE
IF @asTriggerType = 'UPDATE' SELECT @asUpdateJoin = CHAR(9) + 'JOIN Deleted D ON' + RTRIM(@asUpdateJoin)	

SELECT @lsFooter = 
	CHAR(13) 
	+ 'FROM' + CHAR(13)
	+ CHAR(9) + CASE WHEN @asTriggerType = 'DELETE' THEN 'Deleted ' ELSE 'Inserted' END
	+ ' I' + CHAR(13)
	+ RTRIM(@asUpdateJoin) + CHAR(13)
	+ @lsWhere

--Create Trigger
IF @asBody2 IS NULL
	EXEC (@lsHeader + @lsBody1 + @lsBody1a + @lsFooter)
ELSE
	IF @asBody3 IS NULL
		EXEC (@lsHeader + @lsBody1 + @lsBody1a + @lsBody2 + @lsBody2a + @lsFooter)
	ELSE
		IF @asBody4 IS NULL
			EXEC (@lsHeader + @lsBody1 + @lsBody1a + @lsBody2 + @lsBody2a + @lsBody3 + @lsBody3a + @lsFooter)
		ELSE
			EXEC (@lsHeader + @lsBody1 + @lsBody1a + @lsBody2 + @lsBody2a + @lsBody3 + @lsBody3a + @lsBody4 + @lsBody4a + @lsFooter)

--Report progress
IF @anError = 0 
	BEGIN
		PRINT RTRIM(@asTriggerType) + ' trigger for table ' + RTRIM(@asTableName) + ' created'
	END
ELSE
	BEGIN
		PRINT 'Could not create ' + RTRIM(@asTriggerType) + ' trigger for table ' + RTRIM(@asTableName)
		COMMIT
	END

SET NOCOUNT OFF


GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO


CREATE PROCEDURE dbo.utl_AuditTriggerTable
	@cTableName				CHAR(30)
	,@cIsAuditINSTrigger	CHAR(1)
	,@cIsAuditUPDTrigger	CHAR(1)
	,@cIsAuditDELTrigger	CHAR(1)
	,@nError				INT OUTPUT
	AS
/********************************************************************************************************************************************************
Purpose: 	Create all Audit Triggers for given table

Amendment Log
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Date 		Who			Comment
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
27/11/00	JHAYNE		Initial Version
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
********************************************************************************************************************************************************/
DECLARE
	@cProcName 			CHAR(30)
	,@cProgress			VARCHAR(255)
	,@cColName			CHAR(30)
	,@cType				CHAR(30)
	,@cPK				CHAR(1)
	,@cIdentity			CHAR(1)
	,@cIdentityCol		CHAR(30)
	,@cColChar_I		VARCHAR(255)
	,@cColChar_D		VARCHAR(255)
	,@cINSBody1			VARCHAR(8000)
	,@cINSBody2			VARCHAR(8000)
	,@cINSBody3			VARCHAR(8000)
	,@cINSBody4			VARCHAR(8000)
	,@cUPDBody1			VARCHAR(8000)
	,@cUPDBody2			VARCHAR(8000)
	,@cUPDBody3			VARCHAR(8000)
	,@cUPDBody4			VARCHAR(8000)
	,@cUpdateJoin		VARCHAR(1000)
	,@cINSLine			VARCHAR(1000)
	,@cUPDLine			VARCHAR(1000)
	,@cINSVarToUse		TINYINT
	,@cUPDVarToUse		TINYINT
	,@cIsFirstCol		CHAR(1)

--Initialise
SET NOCOUNT ON
SELECT @cProcName = OBJECT_NAME(@@PROCID)
SELECT @nError = 0
SELECT @cINSVarToUse = 1, @cUPDVarToUse = 1
SELECT @cIsFirstCol = 'Y'
SELECT @cINSBody1 = '', @cINSBody2 = '', @cINSBody3 = '', @cINSBody4 = ''
SELECT @cUPDBody1 = '', @cUPDBody2 = '', @cUPDBody3 = '', @cUPDBody4 = ''

--Declare Cursor ***--
DECLARE Columns CURSOR FOR
SELECT 
	C.Name cName
	,ST.Name cType
	,CASE WHEN C.AutoVal IS NULL THEN 'N' ELSE 'Y' END cIdentity
	,CASE WHEN SK.ColID IS NULL THEN 'N' ELSE 'Y' END cPK
FROM
	SysObjects T 
	JOIN SysColumns C ON C.ID = T.ID
	JOIN SysTypes ST ON ST.XUserType = C.XUserType
	LEFT OUTER JOIN SysIndexes SI ON SI.ID = T.ID AND (SI.Status = 2050 or SI.Status = 18450)
	LEFT OUTER JOIN SysIndexKeys SK on SK.id = T.ID AND SK.IndID = SI.IndID AND SK.ColID = C.ColID
WHERE
	T.Name = @cTableName
	AND T.Type = 'U'
ORDER BY
	4 DESC
	,SK.KeyNo ASC
	,C.ColOrder

SELECT @nError = @@ERROR
IF @nError <> 0 
	BEGIN
		PRINT' Could not declare cursor for Columns'
		RETURN
	END

--Open Cursor
OPEN Columns

--Loop through rows and create build up trigger strings
SELECT @cUpdateJoin = '', @cIdentityCol = '0'
WHILE 1=1
	BEGIN
		--Fetch Next Row
		FETCH NEXT FROM Columns INTO @cColName, @cType, @cIdentity, @cPK

		--Exit loop if last row reached
		IF @@FETCH_STATUS  = -1 BREAK

		--Check FETCH_STATUS as expected ***--
		IF @@FETCH_STATUS <> 0 
			BEGIN
				SELECT @nError = -1
				PRINT 'Row fetched is missing when looping through Table columns'
				CLOSE Columns
				DEALLOCATE Columns
				RETURN
			END

		--Ensure a PK exists
		IF @cIsFirstCol = 'Y'
			BEGIN
				IF @cPK = 'N'
					BEGIN
						SELECT @nError = -1
						PRINT 'No Primary Key for table:' + RTRIM(@cTableName)
						CLOSE Columns
						DEALLOCATE Columns
						RETURN
					END
			END

		--Get first Identity Column
		If @cIdentityCol = '0' AND @cIdentity = 'Y'
			BEGIN
				SELECT @cIdentityCol = 'I.' + @cColName
			END
		
		--Convert column to char
		SELECT @cColChar_I = 
			CASE 
				WHEN RTRIM(@cType) = 'char' OR RTRIM(@cType) = 'varchar' OR RTRIM(@cType) = 'nvarchar' OR RTRIM(@cType) = 'text'
					THEN 'I.' + RTRIM(@cColName)
				WHEN RTRIM(@cType) = 'int' OR RTRIM(@cType) = 'smallint' OR RTRIM(@cType) = 'tinyint' OR RTRIM(@cType) = 'decimal' OR RTRIM(@cType) = 'numeric' 
					THEN 'CONVERT(CHAR(30), ' + 'I.' + RTRIM(@cColName) + ')'
				WHEN RTRIM(@cType) = 'datetime'
					THEN 'CASE WHEN CONVERT(CHAR(8), I.' + RTRIM(@cColName) + ', 114) = ''00:00:00'' THEN CONVERT(CHAR(11), ' + 'I.' + RTRIM(@cColName) + ', 113) ELSE CONVERT(CHAR(20), ' + 'I.' + RTRIM(@cColName) + ', 113) END'
				ELSE '?' 
			END
		SELECT @cColChar_D = REPLACE(@cColChar_I, 'I.', 'D.')
		IF RTRIM(@cColChar_I) = '?' 
			BEGIN
				PRINT 'Unexpected datatype: ' + RTRIM(@cType) + ' for column: ' + RTRIM(@cColName) + ' of table:' + RTRIM(@cTableName)
				CLOSE Columns
				DEALLOCATE Columns
				RETURN
			END

		--Primary Key column?
		IF @cPK = 'Y'
			--Primary Key
			BEGIN
				SELECT @cUpdateJoin = RTRIM(@cUpdateJoin) + ' AND D.' + RTRIM(@cColName) + ' = I.' + RTRIM(@cColName)
				SELECT @cINSLine = '+ ''' + CASE WHEN @cIsFirstCol = 'Y' THEN RTRIM(@cColName) ELSE ', ' + RTRIM(@cColName) END + ':'' + RTRIM(' + RTRIM(@cColChar_I) + ')'
				SELECT @cUPDLine = @cINSLine
			END
		ELSE
			--Not a Primary Key
			BEGIN
				IF RTRIM(@cType) <> 'text'
					BEGIN
						SELECT @cINSLine = '+ CASE WHEN ISNULL(' + RTRIM(@cColChar_I) + ', '''') = '''' THEN RTRIM('''') ELSE '', ' + RTRIM(@cColName) + ':'' + RTRIM(' + RTRIM(@cColChar_I) + ') END'
						SELECT @cUPDLine = '+ CASE WHEN ISNULL(' + RTRIM(@cColChar_I) + ', '''') = ISNULL(' + RTRIM(@cColChar_D) + ', '''')  THEN RTRIM('''') ELSE '', ' + RTRIM(@cColName) + ':'' + RTRIM(ISNULL(' + RTRIM(@cColChar_D) + ', ''NULL'')) + ''-'' + RTRIM(ISNULL(' + RTRIM(@cColChar_I) + ', ''NULL'')) END'
					END
				ELSE
					BEGIN
						SELECT @cINSLine = ''
						SELECT @cUPDLine = '' --Text values not available in Inserted and Deleted trigger tables. Therefore decision taken not to audit text fields at all.
					END
			END		

		--Store INS line in a var
		IF @cINSVarToUse = 1
			BEGIN
				--Is there enough room left in current var?
				IF LEN(@cINSLine) + LEN(@cINSBody1) < 6900
					SELECT @cINSBody1 = RTRIM(@cINSBody1) + CHAR(9) + CHAR(9) + CHAR(9) + @cINSLine + CHAR(13) 
				ELSE
					BEGIN
						SELECT @cINSVarToUse = 2
						SELECT @cINSBody2 = CHAR(9) + CHAR(9) + CHAR(9) + @cINSLine + CHAR(13) 
					END
			END
		ELSE
			IF @cINSVarToUse = 2
				BEGIN
					--Is there enough room left in current var?
					IF LEN(@cINSLine) + LEN(@cINSBody2) < 6900
						SELECT @cINSBody2 = RTRIM(@cINSBody2) + CHAR(9) + CHAR(9) + CHAR(9) + @cINSLine + CHAR(13) 
					ELSE
						BEGIN
							SELECT @cINSVarToUse = 3
							SELECT @cINSBody3 = CHAR(9) + CHAR(9) + CHAR(9) + @cINSLine + CHAR(13) 
						END
				END
			ELSE
				IF @cINSVarToUse = 3
					BEGIN
						--Is there enough room left in current var?
						IF LEN(@cINSLine) + LEN(@cINSBody3) < 6900
							SELECT @cINSBody3 = RTRIM(@cINSBody3) + CHAR(9) + CHAR(9) + CHAR(9) + @cINSLine + CHAR(13) 
						ELSE
							BEGIN
								SELECT @cINSVarToUse = 4
								SELECT @cINSBody4 = CHAR(9) + CHAR(9) + CHAR(9) + @cINSLine + CHAR(13) 
							END
					END
				ELSE
					IF @cINSVarToUse = 4
						BEGIN
							--Is there enough room left in current var?
							IF LEN(@cINSLine) + LEN(@cINSBody4) < 6900
								SELECT @cINSBody4 = RTRIM(@cINSBody4) + CHAR(9) + CHAR(9) + CHAR(9) + @cINSLine + CHAR(13) 

							ELSE
								BEGIN
									SELECT @nError = -1
									PRINT 'Overflow of variable space on INS column: ' + RTRIM(@cColName) + ' of table:' + RTRIM(@cTableName)
									CLOSE Columns
									DEALLOCATE Columns
									RETURN
								END
						END

		--Store UPD line in a var
		IF @cUPDVarToUse = 1
			BEGIN
				--Is there enough room left in current var?
				IF LEN(@cUPDLine) + LEN(@cUPDBody1) < 6900
					SELECT @cUPDBody1 = RTRIM(@cUPDBody1) + CHAR(9) + CHAR(9) + CHAR(9) + @cUPDLine + CHAR(13) 
				ELSE
					BEGIN
						SELECT @cUPDVarToUse = 2
						SELECT @cUPDBody2 = CHAR(9) + CHAR(9) + CHAR(9) + @cUPDLine + CHAR(13) 
					END
			END
		ELSE
			IF @cUPDVarToUse = 2
				BEGIN
					--Is there enough room left in current var?
					IF LEN(@cUPDLine) + LEN(@cUPDBody2) < 6900
						SELECT @cUPDBody2 = RTRIM(@cUPDBody2) + CHAR(9) + CHAR(9) + CHAR(9) + @cUPDLine + CHAR(13) 
					ELSE
						BEGIN
							SELECT @cUPDVarToUse = 3
							SELECT @cUPDBody3 = CHAR(9) + CHAR(9) + CHAR(9) + @cUPDLine + CHAR(13) 
						END
				END
			ELSE
				IF @cUPDVarToUse = 3
					BEGIN
						--Is there enough room left in current var?
						IF LEN(@cUPDLine) + LEN(@cUPDBody3) < 6900
							SELECT @cUPDBody3 = RTRIM(@cUPDBody3) + CHAR(9) + CHAR(9) + CHAR(9) + @cUPDLine + CHAR(13) 
						ELSE
							BEGIN
								SELECT @cUPDVarToUse = 4
								SELECT @cUPDBody4 = CHAR(9) + CHAR(9) + CHAR(9) + @cUPDLine + CHAR(13) 
							END
					END
				ELSE
					IF @cUPDVarToUse = 4
						BEGIN
							--Is there enough room left in current var?
							IF LEN(@cUPDLine) + LEN(@cUPDBody4) < 6900
								SELECT @cUPDBody4 = RTRIM(@cUPDBody4) + CHAR(9) + CHAR(9) + CHAR(9) + @cUPDLine + CHAR(13) 
							ELSE
								BEGIN
									SELECT @nError = -1
									PRINT 'Overflow of variable space on UPD column: ' + RTRIM(@cColName) + ' of table:' + RTRIM(@cTableName)
									CLOSE Columns
									DEALLOCATE Columns
									RETURN
								END
						END
		SELECT @cIsFirstCol = 'N'

	END

--Release Cursor
CLOSE Columns
DEALLOCATE Columns

--Finish off INS Body
IF @cINSVarToUse = 1
	SELECT @cINSBody1 = RTRIM(@cINSBody1) + CHAR(9) + CHAR(9) + ',255)' + CHAR(13) + CHAR(9) + ',' + RTRIM(@cIdentityCol)
ELSE
	IF @cINSVarToUse = 2
		SELECT @cINSBody2 = RTRIM(@cINSBody2) + CHAR(9) + CHAR(9) + ',255)' + CHAR(13) + CHAR(9) + ',' + RTRIM(@cIdentityCol)
	ELSE
		IF @cINSVarToUse = 3
			SELECT @cINSBody3 = RTRIM(@cINSBody3) + CHAR(9) + CHAR(9) + ',255)' + CHAR(13) + CHAR(9) + ',' + RTRIM(@cIdentityCol)
		ELSE
			SELECT @cINSBody4 = RTRIM(@cINSBody4) + CHAR(9) + CHAR(9) + ',255)' + CHAR(13) + CHAR(9) + ',' + RTRIM(@cIdentityCol)

--Finish off UPD Body
IF @cUPDVarToUse = 1
	SELECT @cUPDBody1 = RTRIM(@cUPDBody1) + CHAR(9) + CHAR(9) + ',255)' + CHAR(13) + CHAR(9) + ',' + RTRIM(@cIdentityCol)
ELSE
	IF @cUPDVarToUse = 2
		SELECT @cUPDBody2 = RTRIM(@cUPDBody2) + CHAR(9) + CHAR(9) + ',255)' + CHAR(13) + CHAR(9) + ',' + RTRIM(@cIdentityCol)
	ELSE
		IF @cUPDVarToUse = 3
			SELECT @cUPDBody3 = RTRIM(@cUPDBody3) + CHAR(9) + CHAR(9) + ',255)' + CHAR(13) + CHAR(9) + ',' + RTRIM(@cIdentityCol)
		ELSE
			SELECT @cUPDBody4 = RTRIM(@cUPDBody4) + CHAR(9) + CHAR(9) + ',255)' + CHAR(13) + CHAR(9) + ',' + RTRIM(@cIdentityCol)

--Create INSERT trigger for table
IF @cIsAuditINSTrigger = 'Y'
	BEGIN
		EXEC utl_AuditTrigger_Create 
			@cTableName
			,'INSERT'
			,@cINSBody1
			,@cINSBody2
			,@cINSBody3
			,@cINSBody4
			,''
			,@nError OUTPUT
		IF @nError <> 0 RETURN --Error already reported
	END

--Create DELETE trigger for table
IF @cIsAuditDELTrigger = 'Y'
	BEGIN
		EXEC utl_AuditTrigger_Create 
			@cTableName
			,'DELETE'
			,@cINSBody1
			,@cINSBody2
			,@cINSBody3
			,@cINSBody4
			,''
			,@nError OUTPUT
		IF @nError <> 0 RETURN --Error already reported
	END

--Create UPDATE trigger for table
IF @cIsAuditUPDTrigger = 'Y'
	BEGIN
SELECT @cUpdateJoin = SUBSTRING(@cUpdateJoin,5, LEN(@cUpdateJoin) - 4)
		EXEC utl_AuditTrigger_Create 
			@cTableName
			,'UPDATE'
			,@cUPDBody1
			,@cUPDBody2
			,@cUPDBody3
			,@cUPDBody4
			,@cUpdateJoin
			,@nError OUTPUT
		IF @nError <> 0 RETURN --Error already reported
	END

--End Proc
SET NOCOUNT OFF

GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Share

Tags

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Related content

Technical Article

Shows Tables for a Given Database from DOS

If you are using OSQL or ISQL from DOS querys are really hard to use, because the result is to big for the screen. With this procedure, you can especify from wich column to wich column show the list of tables. For Example: proc_tables master, 3, 10 will show you only a short list of […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2001-10-28

728 reads

Technical Article

Run a Script Against All User Databases

Now you can run administrative T-SQL tasks against all your databases in one shot. Very usefull for environments that have a seperate database for each client, ASP's, etc. It's pretty straight forward and can be used for almost any task. This example Looks for a table and prints the database name and selects from the […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2001-10-21

3,072 reads

Technical Article

Find Longstanding Open Transactions

Ever forget to commit a transaction and then find out hours later that there is deadlocked transactions all over your database server? Worse yet has one of your coworkers done this to your database server? Never again, this stored procedure will net send the machine that has an open transaction, and send you (The DBA) […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4 (1)

You rated this post out of 5. Change rating

2001-10-08

8,887 reads