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

Using Variables as column names in select statement Expand / Collapse
Author
Message
Posted Tuesday, November 06, 2007 10:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 6:24 AM
Points: 46, Visits: 151
Hi sql_ppl,

I am creating a trigger and in the trigger I call a sp with params. The sp should update data in another database that has exactly the same table structures (ie multi company, same app). I am going to create a trigger per table that is being updated (as the tables being updated is known to me).

I also allready know which column is being updated, to get the pre and post values of the column I use the Inserted and Deleted tables (which are only available while trigger is running).

The column being updated is passed to a variable and I want to pass the pre and post values to variables as well.

To get my values I use the following:

SET @execSQL = 'DECLARE @BeforeValue sql_variant, @AfterValue sql_variant '+
'SELECT @BeforeValue = D.['+@ColumnName+'], '+
' @AfterValue = I.['+@ColumnName+'] '+
'FROM Inserted I '+
'INNER JOIN Deleted D on I.['+@KeyCol+'] = D.['+@KeyCol+'] '
exec(@execSQL)

Where @ColumnName = column being updated
@KeyCol = Key column that is designated in each trigger

As you can see I cant do this because the exec() statement causes the Inserted and Deleted tables to be outside the scope of the trigger and fails.

My Question (at long last) is: Is there a way to pass the @ColumnName / @KeyCol vars as a actual columns in the select clause, ie

DECLARE @BeforeValue sql_variant, @AfterValue sql_variant
SELECT @BeforeValue = D.[@ColumnName],
@AfterValue = I.[@ColumnName]
FROM Inserted I
INNER JOIN Deleted D on I.[@KeyCol] = D.[@KeyCol]

Your help would be much appreciated.

thx all
Post #419185
Posted Tuesday, November 06, 2007 10:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:26 PM
Points: 12,744, Visits: 31,071
[b]

SET @execSQL = 'DECLARE @BeforeValue sql_variant, @AfterValue sql_variant '+
'SELECT @BeforeValue = D.['+@ColumnName+'], '+
' @AfterValue = I.['+@ColumnName+'] '+
'FROM Inserted I '+
'INNER JOIN Deleted D on I.['+@KeyCol+'] = D.['+@KeyCol+'] '
exec(@execSQL)

Where @ColumnName = column being updated
@KeyCol = Key column that is designated in each trigger

i see a couple of things:
isn't it true that the either the INSERTED table or the DELETED table will always be empty in the trigger, so if you inner join, the two, you'd always get no rows?
since you are declaring a variable, then the trigger would fail if the INSERTED table had more than one row in it's event.

i think you should copy whatever the stored proc was doing, instead of trying to use the stored proc.
and of course make sure the whole thing remails set based instead of assuming only one row would fire the trigger.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #419190
Posted Tuesday, November 06, 2007 11:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 4:20 PM
Points: 132, Visits: 123
MSDN ->
The relational engine may need to build a worktable to perform a logical operation specified in an SQL statement. Worktables are typically generated for certain GROUP BY, ORDER BY, or UNION queries. For example, if an ORDER BY clause references columns not covered by any indexes, the relational engine may need to generate a worktable to sort the result set into the order requested.

Worktables are built in tempdb and are dropped automatically at the end of the statement.

Post #419229
Posted Tuesday, November 06, 2007 12:58 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 4:20 PM
Points: 132, Visits: 123
Ignore my above reply

It's wrongly posted here

Post #419257
Posted Tuesday, November 06, 2007 10:34 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 6:24 AM
Points: 46, Visits: 151
Lowell, as far as i know the inserted and deleted tables will have the details of your post in it while the trigger is firing. Thus the deleted table will have the "previous" value and the inserted table will have the "posted" value. This will work if you are updating say tables used exclusively for lookups, thus, in theory ;) you should be able to get back a single row for the inner join.

The reason I did not want to put the sp in the trigger is maintanance, if something should change I have to change all the triggers instead of say one sp.

thx for the input.
Post #419441
Posted Wednesday, November 07, 2007 4:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:26 PM
Points: 12,744, Visits: 31,071
Thanks Theo; I really need to test that myself , as I clearly don't make enough triggers; I should have known that...i assumed that the tables would only be populated based on the event it was doing, so inserted would have data for insert/update, and deleted would only have the data in the delete event.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #419528
Posted Thursday, November 08, 2007 1:17 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, May 05, 2011 1:38 AM
Points: 1,636, Visits: 604
Hello Theo,
as far as I know it isn't possible, and even if it was, I don't think it would be advisable. A trigger calling SP also isn't the best solution, trigger should contain short pieces of code that can be executed quickly, because triggers tend to be fired pretty often. Dynamic SQL in a trigger is something I wouldn't recommend.
Also, I would be VERY careful with triggers, especially if you are updating another database. If a trigger fails, everything will be rolled back... if I understand it correctly, it means that as soon as the other database is offline for any reason, or is experiencing blocking problems etc., you won't be able to do anything in your database (that is, not on tables with such triggers).

You didn't describe in detail what is the reason for setting up the triggers. Maybe replication, or some other way would accomplish what you need? If you really want to use triggers, the best way out seems to be to use the trigger to insert just the ID of changed row into some "ToDo" table and then use a job that will update the rows in another database (based on current values in your DB) and delete ID from the ToDo table.



Post #419944
Posted Thursday, November 08, 2007 2:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 6:24 AM
Points: 46, Visits: 151
Hi Vladan,

thx for the input, I discussed my proposed solution with client and supervisor and the triggers will only be used on 2 tables as these tables carry counters that are updated in code, thus only the columns that changed must be updated if record exists in other database. the rest of the lookup tables will be repopulated with the master database records with a dts package that calls a sp.

I created a update and insert trigger for the 1st table and tested, works absolutly brilliant. The only reason that I wanted to call alias.@column was to make the scripts generic, as there are only 2 tables that are going to use the triggers I decided to make unique triggers for each, 4 triggers to maintain isn't that big of a deal for me so I am happy with the result.

PS I also considered using an audit table as you suggested, but my supervisor did not agree, so what the boss wants the boss gets i suppose.

thx again for the input.
Post #419953
Posted Thursday, November 08, 2007 3:37 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, April 13, 2014 9:51 PM
Points: 4,570, Visits: 8,315
Lowell (11/7/2007)
Thanks Theo; I really need to test that myself , as I clearly don't make enough triggers; I should have known that...i assumed that the tables would only be populated based on the event it was doing, so inserted would have data for insert/update, and deleted would only have the data in the delete event.

deleted has data in the update/delete event.
Post #419966
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse