Hi, Can I get information about the connection that fired the trigger ? Especially the connection name. On an after update trigger can I get the name of the column(s) that are updated ? Thanks in advence. |
You can get the connection name via connection_property('Name'). Thanks for you reply. I try your method to know witch culomn is updated but it seems that the "If UPDATE(col) Then" works only if we pass the column name and not a variable with the column name. I try that but it dont pass inside the IF : FOR lpp AS ColCurs CURSOR FOR SELECT cname FROM sys.SYSCOLUMNS WHERE Creator = 'DBA' AND tname = 'CLI' DO SET wCol = cname; IF UPDATE(wCol) THEN SET wValAv = 'AV'; SET wValAp = 'Ap'; END IF; END FOR;
(24 Oct '18, 06:34)
Ben8sens
Replies hidden
You can use the UPDATING() function here which expects the column name as string whereas UPDATE() expects an identifier. For an example, see the second FAQ I cited in my answer.
(24 Oct '18, 07:03)
Volker Barth
I try "IF UPDATING(wCol) THEN", but I have an -143 Syntax error near 'wCol'. Here wCol is a long nvarchar.
(24 Oct '18, 13:19)
Ben8sens
Hm, in my tests with 17.0.9.4857, I could avoid the syntax error but within a cursor loop, the UPDATE search condition does not seem to work properly. Here's my attempt with a trigger on the sample database's Products table, with several debug messages within: create or replace trigger TU_Products after update on GROUPO.Products referencing old as o new as n for each row begin message 'Trigger "TU_Products" fired' to log; for lpp as crs cursor for select column_name as @ColName from SYS.SYSTABCOL SC key join SYS.SYSTAB where creator = user_id('GROUPO') and table_name = 'Products' order by column_id for read only do message 'Trigger "TU_Products" checks whether column "' || @ColName || '" was modified.' to log; if update(@ColName) then message 'Trigger "TU_Products": Column "' || @ColName || '" was modified.' to log; end if; end for; if update(Quantity) then message 'Trigger "TU_Products": Column "' || 'Quantity' || '" was modified.' to log; end if; message 'Trigger "TU_Products" finished' to log; end; -- Update one row update Products set Quantity = Quantity - 1, UnitPrice = UnitPrice + 0.50 where ID = 300; -- display the last 11 log messages select msg_text from sa_server_messages(null, -11) order by msg_id;
(25 Oct '18, 04:15)
Volker Barth
The test shows the following output: Trigger "TU_Products" fired Trigger "TU_Products" checks whether column "ID" was modified. Trigger "TU_Products" checks whether column "Name" was modified. Trigger "TU_Products" checks whether column "Description" was modified. Trigger "TU_Products" checks whether column "Size" was modified. Trigger "TU_Products" checks whether column "Color" was modified. Trigger "TU_Products" checks whether column "Quantity" was modified. Trigger "TU_Products" checks whether column "UnitPrice" was modified. Trigger "TU_Products" checks whether column "Photo" was modified. Trigger "TU_Products": Column "Quantity" was modified. Trigger "TU_Products" finished Apparently, the "if update(@ColName)" test did not succeed for the "Quantity" column whereas the test via "if update(Quantity)" does - as does the test via the alternate syntax with "if updating('Quantity')".
(25 Oct '18, 04:17)
Volker Barth
Comment Text Removed
There is no column named @ColName so UPDATE ( @ColName ) might be expected to return false. I don't know if EXECUTE IMMEDIATE will work, but it's the next Dead Chicken to try :)
(25 Oct '18, 10:39)
Breck Carter
Well, being on v17, I had tried with the cool indirect identifier feature but could not provide valid syntax...
(25 Oct '18, 11:13)
Volker Barth
The replies are hidden :(
(25 Oct '18, 12:48)
Ben8sens
Hm? What do you mean?
(25 Oct '18, 14:11)
Volker Barth
|