Dynamic trigger creation woes...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamic trigger creation woes...

Author

Topic

cheuschober
Starting Member

5 Posts

Posted - 2007-12-06 : 14:18:54

Hi. New to the forum and semi-new to sqlserver.

I have a semi-large mssql db (that fills several roles) with thirty or so tables and would like to have some /basic/ record checks on last edit date and edit by stored on the record level in each table. I know it won't catch deletes but I'm the only user who can execute those (data checks in...).

In any case I had the bright idea to add an 'edited' and 'edited_by' field to each table in the db and then to also dynamically create a pair of triggers on each of these tables for INSERT and UPDATE to write the username and the datetime of the action.

Individually, I have all the pieces put together. I used sysobjects to get my tables and information_schema to pull out my primary keys (with a little cursor work for those tables with multiple-field keys). And everything should be all bundled up neatly to run inside a cursor and just batch-update every table in the system this way (which I wouldn't mind turning to some of the other systems I've recently inherited).

The problem, of course, is the restriction that CREATE TRIGGER must be issued as the first statement of a batch and if I issue a 'GO' it'll kill my cursors (or at least their variables) which are all needed to dynamically create the trigger.

Essentially it's the same ALTER TABLE and CREATE TRIGGER statement for each table but the targets and trigger names change dynamically.

Is there any way around this restriction?

Regards and thanks,
Chad

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-06 : 14:47:50

It's hard to help without seeing some code.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

cheuschober
Starting Member

5 Posts

Posted - 2007-12-06 : 14:50:56

Totally understand... here's the mockup as-is:

DECLARE @tname NVARCHAR(255)
DECLARE c1 CURSOR
LOCAL STATIC READ_ONLY FOR
SELECT name FROM sysobjects WHERE type = N'U'

OPEN c1
FETCH NEXT FROM c1 INTO @tname
WHILE @@FETCH_STATUS = 0
BEGIN

EXEC ('ALTER TABLE '+@tname+' ADD updated SMALLDATETIME NULL DEFAULT GETDATE()')
EXEC ('ALTER TABLE '+@tname+'ADD updated_by VARCHAR(64) NULL')

DECLARE @whereclause VARCHAR(1000)

DECLARE @kname NVARCHAR(255)
DECLARE c2 CURSOR
LOCAL STATIC READ_ONLY FOR
SELECT ky.COLUMN_NAME FROM information_schema.table_constraints tbl
INNER JOIN information_schema.key_column_usage ky
ON tbl.constraint_name = ky.constraint_name
WHERE
tbl.constraint_type = 'PRIMARY KEY' AND tbl.table_name = @tname

OPEN c2

FETCH NEXT FROM c2 INTO @kname
SET @whereclause = ' WHERE '+@kname+' IN (SELECT '+@kname+' FROM inserted)'

FETCH NEXT FROM c2 INTO @kname
WHILE @@FETCH_STATUS = 0

BEGIN
SET @whereclause = @selectkeys+' AND '+@kname+' IN (SELECT '+@kname+' FROM inserted)'
FETCH NEXT FROM c2 INTO @kname
END

CLOSE c2
DEALLOCATE c2
GO

CREATE TRIGGER @tname+N'_insertuser'
ON @tname
FOR INSERT
AS
BEGIN
EXEC ('UPDATE '+@tname+' SET updated_by=USER_NAME(USER_ID())'+@selectkeys)
END
GO

CREATE TRIGGER @tname+N'_updaterecord'
ON @tname
FOR UPDATE
AS
BEGIN
IF NOT UPDATE(updated)
EXEC ('UPDATE '+@tname+' SET updated=GETDATE(),updated_by=USER_NAME(USER_ID())'+@selectkeys)
END
GO

END
CLOSE c1
DEALLOCATE c1

It fails, as I said, because of the batching issues. Any help would be appreciated.

cheuschober
Starting Member

5 Posts

Posted - 2007-12-06 : 14:54:43

Ack. Scratch that. That wasn't my most recent attempt and you'll get an undeclared variable with that. (whereclause / selectkeys)

Here's the most recent attempt:

DECLARE @tname NVARCHAR(255)
DECLARE c1 CURSOR
LOCAL STATIC READ_ONLY FOR
SELECT name FROM sysobjects WHERE type = N'U'

OPEN c1
FETCH NEXT FROM c1 INTO @tname
WHILE @@FETCH_STATUS = 0
BEGIN

EXEC ('ALTER TABLE '+@tname+' ADD updated SMALLDATETIME NULL DEFAULT GETDATE()')
EXEC ('ALTER TABLE '+@tname+'ADD updated_by VARCHAR(64) NULL')

DECLARE @whereclause VARCHAR(1000)

DECLARE @kname NVARCHAR(255)
DECLARE c2 CURSOR
LOCAL STATIC READ_ONLY FOR
SELECT ky.COLUMN_NAME FROM information_schema.table_constraints tbl
INNER JOIN information_schema.key_column_usage ky
ON tbl.constraint_name = ky.constraint_name
WHERE
tbl.constraint_type = 'PRIMARY KEY' AND tbl.table_name = @tname

OPEN c2

FETCH NEXT FROM c2 INTO @kname
SET @whereclause = ' WHERE '+@kname+' IN (SELECT '+@kname+' FROM inserted)'

FETCH NEXT FROM c2 INTO @kname
WHILE @@FETCH_STATUS = 0

BEGIN
SET @whereclause = @whereclause+' AND '+@kname+' IN (SELECT '+@kname+' FROM inserted)'
FETCH NEXT FROM c2 INTO @kname
END

CLOSE c2
DEALLOCATE c2
GO

CREATE TRIGGER @tname+N'_insertuser'
ON @tname
FOR INSERT
AS
BEGIN
EXEC ('UPDATE '+@tname+' SET updated_by=USER_NAME(USER_ID())'+@whereclause)
END
GO

CREATE TRIGGER @tname+N'_updaterecord'
ON @tname
FOR UPDATE
AS
BEGIN
IF NOT UPDATE(updated)
EXEC ('UPDATE '+@tname+' SET updated=GETDATE(),updated_by=USER_NAME(USER_ID())'+@whereclause)
END
GO

END
CLOSE c1
DEALLOCATE c1

Regards,
~Chad

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-06 : 14:57:33

Why don't you just put default values on your columns so that you don't need to use triggers? Triggers are typically considered a bad idea.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

cheuschober
Starting Member

5 Posts

Posted - 2007-12-06 : 15:02:39

Because I'm trying to capture the date/time any record was updated/created and the user who made that change. A default won't catch the update and though I could, I guess, create a stored procedure for every table in the system to wrap all activities that would be just as tedious as using the triggers to keep record-level update/insert information in-tact.

Regards,
~Chad

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-06 : 15:07:46

There is no good way to fix your dynamic trigger issue.

My recommendation would be to provide this information during the DML operations.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

cheuschober
Starting Member

5 Posts

Posted - 2007-12-06 : 15:15:39

Guess I'm going to have to do it by hand. The db has too many unstable elements to accurately predict all transactions (hence the preference for triggers)... many of ad-hoc bulk imports/updates or direct table-edits (out of necessity / short staffing / changing needs / etc... certainly not by design).

In any case, I appreciate your time and help.

Thanks,
~Chad

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources