Question Re: Informations inside trigger in Technology Q&A

Informations inside trigger

former_SQLA_member1694891 — Tue, 16 Oct 2018 12:10:36 GMT

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.

Re: Informations inside trigger

VolkerBarth — Tue, 16 Oct 2018 14:06:05 GMT

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

For the second question, see that FAQ and that.

Re: Informations inside trigger

former_SQLA_member1694891 — Wed, 24 Oct 2018 06:34:59 GMT

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;

Re: Informations inside trigger

VolkerBarth — Wed, 24 Oct 2018 07:03:09 GMT

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.

Re: Informations inside trigger

former_SQLA_member1694891 — Wed, 24 Oct 2018 13:19:07 GMT

I try "IF UPDATING(wCol) THEN", but I have an -143 Syntax error near 'wCol'. Here wCol is a long nvarchar.

Re: Informations inside trigger

VolkerBarth — Thu, 25 Oct 2018 04:15:33 GMT

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;

Re: Informations inside trigger

VolkerBarth — Thu, 25 Oct 2018 04:17:19 GMT

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')".

Re: Informations inside trigger

Breck_Carter — Thu, 25 Oct 2018 10:39:12 GMT

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 🙂

Re: Informations inside trigger

VolkerBarth — Thu, 25 Oct 2018 11:13:25 GMT

Well, being on v17, I had tried with the cool indirect identifier feature but could not provide valid syntax...

Re: Informations inside trigger

former_SQLA_member1694891 — Thu, 25 Oct 2018 12:48:28 GMT

The replies are hidden 😞

Re: Informations inside trigger

VolkerBarth — Thu, 25 Oct 2018 14:11:10 GMT

Hm? What do you mean?