Is this possible within a Trigger

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)

Is this possible within a Trigger

Author

Topic

rohan_man
Starting Member

8 Posts

Posted - 2007-05-28 : 22:38:55

Hi all,

I have the following trigger:

ALTER TRIGGER [UpdateValues] ON [dbo].[TBLOBJECT]
AFTER INSERT, UPDATE

AS

set nocount on

declare @TableName char(100)
declare @PKValue int

select @TableName = alt.TABLEName
FROM TBLOBJECT o INNER JOIN ALT
ON o.TYPE = alt.TYPE inner join inserted i
on o.OBJECTID = i.OBJECTID

if @TableName<> ''
begin
select @PKValue = objectkeyid from inserted
if @TableName = 'AVIATION'
begin
UPDATE dbo.[AVIATION]
SET
ASSET_NUMBER=(SELECT OBJECTNAME
FROM inserted),

ASSET_DESCRIPTION=(SELECT OBJECTNAME2
FROM inserted)

WHERE PK_AVIATION = @PKValue
end

else if @TableName = 'TREE'
begin
UPDATE dbo.[TREE]
SET
ASSET_NUMBER=(SELECT OBJECTNAME
FROM inserted),
ASSET_DESCRIPTION=(SELECT OBJECTNAME2
FROM inserted)

WHERE PK_TREE = @PKValue
end

As you can see, I check for the table name and then perform the update on the table found. (duplicated code)

My question is this. Is there a way to dynamically do this?
I have tried the following

UPDATE dbo.[@TableName]
SET
ASSET_NUMBER=(SELECT OBJECTNAME
FROM inserted),
ASSET_DESCRIPTION=(SELECT OBJECTNAME2
FROM inserted)
WHERE @FieldName = @PKValue

but it doesn't like @TableName or @FieldName.

Does anyone know if this can be done in the first place? Have an alternative? Or will I need to specify separate statements for each table (like I'm currently doing)?

Cheers

Rohan

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-28 : 23:32:17

Triggers are tied to the tables. So if you need to audit multiple tables, you need to create triggers on each of the tables separately. You cannot do it dynamically in one trigger. As you can clearly see, the syntax for the trigger is: ALTER TRIGGER ON <Table>. So the trigger will fire for the corresponding event on the table on which it is defined.

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

rohan_man
Starting Member

8 Posts

Posted - 2007-05-29 : 00:00:45

Hi,

Cheers for your reply, but I don't think you quite see what the problem is.

I realise that the trigger is tied to the table, but this seems like a special case to me as depending on which table the row is for will determine which table needs to be updated. Let me try and explain this a little more...

Say I have the table tblObject, and I have tables tblAviation, tblTree, tblPipe.

For each row in the tables tblAviation, tblTree, tblPipe, there is a corresponding row in tblObject (ie the values of the primary keys in tblAviation, tblTree, tblPipe are all stored in tblObject in the field ObjectKeyID, there is also a field called SourceTable which stores the name of the table that the row of data is for ie tblTree, tblPipe, tblAviation). So the trigger on tblObject is set to fire when an update or insert is done (which is correct, as I want the trigger to fire whenever a row in tblObject is changed, inserted). As soon as the trigger fires, it checks the column SourceTable in tblObject to get the table for which the data belongs to in tblAviation, tblPipe, tblTree. It then updates the row found in the table found based on the value of the ObjectKeyID field.

That's why I'm trying to do this:

UPDATE dbo.[@TableName]
SET
ASSET_NUMBER=(SELECT OBJECTNAME
FROM inserted),
ASSET_DESCRIPTION=(SELECT OBJECTNAME2
FROM inserted)
WHERE @FieldName = @PKValue

However it doesn't like the variables @TableName and @FieldName.

I could just use the code in my original post and have a separate begin....end section for each table, however there is potentially 30+ tables, and if new tables are added, I don't want to have to modify the trigger.

I hope this explains it a bit better.

Cheers

Rohan

Kristen
Test

22859 Posts

Posted - 2007-05-29 : 04:40:07

Note that you need to write your triggers to handle MULTIPLE rows, so:

select @TableName = alt.TABLEName
FROM TBLOBJECT o INNER JOIN ALT
ON o.TYPE = alt.TYPE inner join inserted i
on o.OBJECTID = i.OBJECTID

will ONLY handle ONE row (randomly chosen from the rows in [inserted]. This is fine when you only insert/update one row, but is not safe the rest of the time.

IMHO having a SINGLE table for all the audited data (if I have understood that's what you want to do - sorry haven't read this thread as carefully as I might!) is a very bad idea. The performance is awful, the reporting is very difficult, the space used is enormous.

There is some discussion here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Triggers,Trigger,audit%20changes

Kristen

rohan_man
Starting Member

8 Posts

Posted - 2007-05-29 : 19:36:45

Hey Kristen,

Thanks for your input. Fortunately the application (well from what I know of it anyway [i didn't write it]) only inserts one row at a time, so the trigger will not need to handle multiple rows, so it should be ok.

However, do you know if my original question regarding dynamically setting up the trigger will work? The trigger is always on the table tblObject, however it needs to update different tables depending on what data is contained in that row. From what I have heard/read, it seems this is not possible.

Also, thanks for the link.

Cheers

Rohan

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-05-29 : 20:54:46

if you don't write your trigger to handle multiple rows, you are writing a bug into your database for someone else to discover down the line. they won't have nice things to say when they discover it!

EDIT: typos

www.elsasoft.org

Kristen
Test

22859 Posts

Posted - 2007-05-30 : 04:14:25

"so it should be ok"

Nonsense. NEVER write a trigger that assumes EVERY update is a single row.

The application may/will change.

Other applications may use this database

Someone will "tidy up the data" with a bulk UPDATE, sooner or later.

"do you know if my original question regarding dynamically setting up the trigger will work...
... Also, thanks for the link."

Didn't the link answer the question?

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources