Problem with updating a table in 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)

Problem with updating a table in a trigger

Author

Topic

eni
Starting Member

5 Posts

Posted - 2007-11-12 : 07:06:14

Hello,

I have a trigger-problem with the following code. The code below works fine, but I'd like to automate it as follows:

When the STATUS in TEST_TABLE changes, TRIGGER_NAME and VALUE are fetched from TRIGGER_TABLE according to the COMP AND STATUS fields of the (updated) Inserted-table. The field name that is the same than the fetched TRIGGER_NAME is updated in TEST_TABLE and gets a new value (namely VALUE). The problem is that basicly there can be many different records in TRIGGER_TABLE AND inserting/updating records in TRIGGER_TABLE needs the trigger to be updated (new IF-clauses according to FIELDx..).

I would like to do the UPDATE-clauses like this:
UPDATE TEST_TABLE SET @trigger_name = @value, @trigger_name + '_DTE' = GETDATE()
WHERE FIELD_ID IN (SELECT FIELD_ID FROM Inserted) AND COMP IN (SELECT COMP FROM Inserted)
END

.. but SQL-queries, where is no exact field name after SET, are not allowed. Can I use other techniques, can system tables help in this case?

Regards,

-Esa

---- code starts here ----

CREATE TRIGGER TESTTRIGGER ON TEST_TABLE
FOR UPDATE
AS
IF NOT UPDATE(STATUS)
BEGIN
RETURN
END
ELSE
BEGIN

DECLARE @trigger_name varchar(20)
DECLARE @value varchar(10)

SELECT @trigger_name = (SELECT TRIGGER_NAME FROM TRIGGER_TABLE WHERE COMP IN (SELECT COMP FROM Inserted) AND LAUNCH_STATUS IN (SELECT STATUS FROM Inserted))
SELECT @value = (SELECT VALUE FROM TRIGGER_TABLE WHERE COMP IN (SELECT COMP FROM Inserted) AND LAUNCH_STATUS IN (SELECT STATUS FROM Inserted))

IF (@trigger_name = '') OR (@value = '')
BEGIN
RETURN
END
ELSE

IF @trigger_name = 'FIELD1'
BEGIN
UPDATE TEST_TABLE SET FIELD1 = @value, FIELD1_DTE = GETDATE()
WHERE FIELD_ID IN (SELECT FIELD_ID FROM Inserted) AND COMP IN (SELECT COMP FROM Inserted)
END
ELSE
IF @trigger_name = 'FIELD2'
BEGIN
UPDATE TEST_TABLE SET FIELD2 = @value, FIELD2_DTE = GETDATE()
WHERE FIELD_ID IN (SELECT FIELD_ID FROM Inserted) AND COMP IN (SELECT COMP FROM Inserted)
END
END

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-12 : 09:48:58

thoroughly confused, what is the higher level purpose for all this?
simple answer, use dynamic sql

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-11-12 : 10:07:47

I'm speechless.

It always amazes me, the number of creative ways people come up with for abusing triggers.

eni, explain the purpose of this code. What is the business function you are trying to accomplish?

e4 d5 xd5 Nf6

eni
Starting Member

5 Posts

Posted - 2007-11-12 : 10:34:46

I studied some dynamic SQL that was new for me. Actually I solved the problem with following changes:

DECLARE @SQL VarChar(1000)
SELECT @SQL = 'UPDATE TEST_TABLE SET '
SELECT @SQL = @SQL + @trigger_name
SELECT @SQL = @SQL + ' = '''
SELECT @SQL = @SQL + @value
SELECT @SQL = @SQL + ''' WHERE FIELD_ID IN ('''
SELECT @SQL = @SQL + @field
SELECT @SQL = @SQL + ''') AND COMP IN ('''
SELECT @SQL = @SQL + @comp
SELECT @SQL = @SQL + ''')'
EXEC(@SQL)

.. with @field and @comp declared before.

I think this is meant by dynamic SQL? The meaning of this trigger is just that when status in TEST_TABLE changes, TRIGGER_TABLE is examined if the user has updated related triggers (according to the status), picks the correct fields (TRIGGER_NAME) and changes field value in TEST_TABLE. Allows the normal user not needed to touch the code :)

But thank you anyway!

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-12 : 10:55:30

What about a normal DBA that has to clean that up down the road?

Allowing user's to update triggers seems like a real bad idea.

Poor planning on your part does not constitute an emergency on my part.

eni
Starting Member

5 Posts

Posted - 2007-11-12 : 12:49:03

quote:
Originally posted by dataguru1971

What about a normal DBA that has to clean that up down the road?

Allowing user's to update triggers seems like a real bad idea.

Well.. ok, my mistake, "normal users" are not allowed to update these triggers. Just those "main users", one or two, who set up triggers.. however they don't have the possibility to access the code. Triggers seem useful in this case, I think.

Just give better ideas to get to the same result if triggers are not the best answer!

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-12 : 12:59:47

quote:
Originally posted by eni

...meaning of this trigger is just that when status in TEST_TABLE changes, TRIGGER_TABLE is examined if the user has updated related triggers (according to the status), picks the correct fields (TRIGGER_NAME) and changes field value in TEST_TABLE. Allows the normal user not needed to touch the code :)

But thank you anyway!

I would be happy to provide better solution, but this is confusing. Maybe your use of the word TRIGGER is causing the confusion.

If I understand correctly
1. Test Table Status changes (presuming you mean new records?)
2. Trigger_Table is examined if the user has updated related triggers according to the status. (what does that mean? )
3. Picks the correct fields (okay, do you mean column names?, or values in the Trigger Name column?)
4. Updates a value in test table based on all of that.

If you can show or post the structure of these tables with sample data, sample scenario, and the actual end result of what you describe, I would bet there are a number of ways to do it differently, if not certainly ways to do it without a trigger.

Poor planning on your part does not constitute an emergency on my part.

eni
Starting Member

5 Posts

Posted - 2007-11-12 : 14:04:06

quote:
Originally posted by dataguru1971

quote:
Originally posted by eni

...meaning of this trigger is just that when status in TEST_TABLE changes, TRIGGER_TABLE is examined if the user has updated related triggers (according to the status), picks the correct fields (TRIGGER_NAME) and changes field value in TEST_TABLE. Allows the normal user not needed to touch the code :)

But thank you anyway!

I would be happy to provide better solution, but this is confusing. Maybe your use of the word TRIGGER is causing the confusion.

If I understand correctly
1. Test Table Status changes (presuming you mean new records?)
2. Trigger_Table is examined if the user has updated related triggers according to the status. (what does that mean? )
3. Picks the correct fields (okay, do you mean column names?, or values in the Trigger Name column?)
4. Updates a value in test table based on all of that.

If you can show or post the structure of these tables with sample data, sample scenario, and the actual end result of what you describe, I would bet there are a number of ways to do it differently, if not certainly ways to do it without a trigger.

Ok, I'll provide some example. Sorry if it's difficult to understand my explanation. As I'm not native English-speaker, and (not yet so pro in coding!) I'm lacking of correct words. I'll try to figure out.

For the first point: no, not actually new records are inserted in Test Table in this case. Test Table has a field (or column name) called STATUS that is always '00' when a new record is added. All records have a status from '00' to '90'. When the status is changed to, say for example '50', a field, say 'ED01' is launched and it gets the value 'Y' and 'ED01DTE' gets timestamp (before the ED01 and ED01DTE-fields had NULL values). At the moment all those fields, ED01, LA01, etc.. (some 5 together) are changed in the code so some automation is desired. Test Table contains altogether dozens of columns as it is one of the main tables.

My proposion is to make a trigger table where there are columns COMP, TRIGGER_NAME, VALUE and LAUNCH_STATUS (or just STATUS), all varchars, COMP and TRIGGER_NAME as primary keys. Each COMP has its own status-related ED01, LA01, etc. triggers (records in trigger table). For example COMP = '1', TRIGGER_NAME = 'ED01', VALUE = 'Y' and STATUS = '50'. And say, COMP = '1', TRIGGER_NAME = 'LA01', VALUE = 'C' and STATUS = '70'.. and for COMP = '2' trigger names (as they are column names in Test Table) are the same, but values and statuses can be different than for comp 1.

The "main users", not all, have the access to insert and update the trigger table as they want ED01, LA01 etc columns in Test Table to be changed as they want.

Hope this explains enough :)

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-12 : 14:28:44

Okay,

Seems the use of the word Trigger is the problem. Your control table contains more or less the instructions for how to apply updates.

so what happens is:
1) A new record is added, and by default, status = '00'
2) When this status is changed later, this requires a number of other field values to change at the same time
3) your proposed trigger finds the status and comp conditions from the trigger_table and uses values to identify both the column to update and the value using dynamic SQL to build the statement.

Seems to me your structure or design is flawed somewhat.

I would rather see the update being applied based on joining to a control table given conditions, if the conditions are always changing, this table could be updated as needed.

I would rather see a control table which had STATUS,COMP,ED01,LA01 etc and you could just have a fixed update statement which did a JOIN on Status and COMP for example. This opposed to dynamically building the query each time in a trigger. Reason being is dynamic SQL has to recompile each time the trigger is fired, and this is not efficient and should be avoided (especially on a trigger).

Poor planning on your part does not constitute an emergency on my part.

eni
Starting Member

5 Posts

Posted - 2007-11-13 : 05:31:33

quote:
Originally posted by dataguru1971

I would rather see the update being applied based on joining to a control table given conditions, if the conditions are always changing, this table could be updated as needed.

I would rather see a control table which had STATUS,COMP,ED01,LA01 etc and you could just have a fixed update statement which did a JOIN on Status and COMP for example. This opposed to dynamically building the query each time in a trigger. Reason being is dynamic SQL has to recompile each time the trigger is fired, and this is not efficient and should be avoided (especially on a trigger).

Ok, you're right. I just tested your proposion by building into a code a SELECT.. JOIN -command to get right LA01, ED01 etc. values from this control table, and then UPDATING Test Table with these new values (according to status). Works fine. I admit that this doesn't need so much capacity and is more efficient. I'm not sure if it is needed Ed01, la01, etc. fields to be updated directly from the database (by management studio or so..) but I think it is not necessary. Updating in the program is more common.

Thank you for your suggestion!

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-13 : 13:42:49

quote:
Originally posted by eni

[

Ok, you're right. I just tested your proposion by building into a code a SELECT.. JOIN -command to get right LA01, ED01 etc. values from this control table, and then UPDATING Test Table with these new values (according to status). Works fine. I admit that this doesn't need so much capacity and is more efficient. I'm not sure if it is needed Ed01, la01, etc. fields to be updated directly from the database (by management studio or so..) but I think it is not necessary. Updating in the program is more common.

Thank you for your suggestion!

Glad to help. Now you know why we kept asking...

I am not sure what that last part meant...I would assume you have the done in sproc, which CAN be fired from a trigger if need be. The idea was to remove all of the unecessary overhead to process the transaction.

Poor planning on your part does not constitute an emergency on my part.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-11-13 : 16:25:14

quote:
Originally posted by dataguru1971

Reason being is dynamic SQL has to recompile each time the trigger is fired, and this is not efficient and should be avoided (especially on a trigger).

I agree with everything you said and a trigger is not the way to go. I don't want to start a debate, but you should be careful about making blanket statements regarding dynamic sql and its performance. I personally hate dynamic sql, but, in general, it is not about performance rather it is about maintainability and access control. (Yes, there are exceptions).

Here is just one offering on the topic that has been discussed to death: [url]http://weblogs.asp.net/aaguiar/archive/2006/06/22/Stored-Procs-vs-Dynamic-SQL.aspx[/url]

PS - Please do not take this is a slam on you, I'm just trying to help educate my fellow database professionals. :)

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-13 : 16:51:45

quote:
Originally posted by Lamprey

quote:
Originally posted by dataguru1971

Reason being is dynamic SQL has to recompile each time the trigger is fired, and this is not efficient and should be avoided (especially on a trigger).

I agree with everything you said and a trigger is not the way to go. I don't want to start a debate, but you should be careful about making blanket statements regarding dynamic sql and its performance. I personally hate dynamic sql, but, in general, it is not about performance rather it is about maintainability and access control. (Yes, there are exceptions).

Here is just one offering on the topic that has been discussed to death: [url]http://weblogs.asp.net/aaguiar/archive/2006/06/22/Stored-Procs-vs-Dynamic-SQL.aspx[/url]

PS - Please do not take this is a slam on you, I'm just trying to help educate my fellow database professionals. :)

No worries, you are right. I shouldn't have made it a blanket statement like that, but I was catering to the OP's level understanding. I will be more cautious in the future--thanks for the heads up!

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources