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.

asked 16 Oct '18, 12:10

Ben8sens's gravatar image

Ben8sens
166121320
accept rate: 44%

edited 16 Oct '18, 12:12


You can get the connection name via connection_property('Name').

For the second question, see that FAQ and that.

permanent link

answered 16 Oct '18, 14:06

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 16 Oct '18, 14:09

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
showing 1 of 9 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×79

question asked: 16 Oct '18, 12:10

question was seen: 1,233 times

last updated: 25 Oct '18, 14:11