I need to write a trigger to update only the fields that have changed in a given table, does anyone have an example of how I do?

explanation: I'm using ASA-9.0.2.3951

I have a table of customer records (which is why the company) and that table when a new record is inserted into a client replication is done for another company in the event of a data update existing records is upgraded joined the company to rise and the company that replicates. This routine replication is performed by a trigger. I would like to upgrade (using the trigger) only the fields that have changed since the TBCLIENT table has 170 fields. I thought of something more dynamic to not have to enter the field for the table field. Below the trigger code.

create TRIGGER "UPDATE_CLIENT" on DBA.TBCLIENT for update
as
begin
  declare @VALUE_NEW text
  declare @VALUE_OLD text
  declare @ColUMNS text
  declare @VALUES text
  declare @IDEMP char(3)
  declare @EMPCURRENT char(3)
  declare @IDCLIENT char(6)
  declare @VALUEFIELD text
  declare @NAMECOLUMN varchar(100)
  declare @Replica varchar(1)
  declare @cpfcgc char(14)
  declare Cursor_FIELDS scroll cursor for select syscolumn.column_name from SYSCOLUMN key join sysdomain where TABLE_ID = (select TABLE_ID from SYSTABLE where TABLE_NAME = 'TBCLIENT') order by column_id asc
  declare Cursor_Emp dynamic scroll cursor for select CODIGO from TBGEREMPRESA where REPLICA_CLIFOR = 'S' and CODIGO <> @EMPCURRENT
  declare Cursor_Replica dynamic scroll cursor for select Replica_Clifor from TBGEREMPRESA where CODIGO = (select EMPRESA from INSERTED)
  declare CurUp dynamic scroll cursor for select Empresa,Codigo,cpf_cgc from Inserted
  open CurUp
  fetch next CurUp into @EMPCURRENT,@IDCLIENT, @cpfcgc
  close CurUp
  open Cursor_Emp
  open Cursor_Replica
  fetch next Cursor_Replica into @Replica
  while @Replica = 'S'
    begin
      fetch next Cursor_Emp into @IDEMP
      if sqlstate = '02000' break
      select @COLUMNS=''
      select @ValUes=''
      select @VALUE_NEW=''
      select @VALUE_OLD=''
      open Cursor_FIELDS
      while 1 = 1
        begin
          fetch next Cursor_FIELDS into @NAMECOLUMN
          if sqlstate = '02000' break
          if @COLUMNS <> ''
            select @COLUMNS=@COLUMNS+','
          if((@NAMECOLUMN <> 'EMPRESA') and(@NAMECOLUMN <> 'CODIGO'))
            begin
               select @VALUE_OLD='(select '+@NAMECOLUMN+' from deleted)'
               select @VALUE_NOVO='(select '+@NAMECOLUMN+' from inserted)'
               if @VALUE_NEW <> @VALUE_OLD   //---> At this point I am not able to make the comparison of new <> old
                  select @COLUMNS=@COLUMNS+' '+@NAMECOLUMN+'=(select '+@NAMECOLUMN+' from inserted)'
            end
        end
      close Cursor_FIELDS
      execute('UPDATE TBCLIENT SET '+@COLUMNS+' where empresa='''+@IDEMP+''' and codigo='''+@IDCLIENT+''' and cpf_cgc='''+@cpfcgc+'''')
    end
  close Cursor_Emp
end

asked 05 Apr '13, 10:32

Walmir%20Taques's gravatar image

Walmir Taques
640293147
accept rate: 13%

edited 05 Apr '13, 19:49

Mark%20Culp's gravatar image

Mark Culp
23.4k9132275


Could you be more specific:

  • What version are you using?
  • You mean you want to change these values within the trigger that have been given a different value by the triggering UPDATE statement (i.e. you want to overwrite the values again)? - Or do you just want to rollback the modifications?
  • Do you want to take an action if any of a list of columns is modified?
  • Or are the desired actions column-specific, such as:
create trigger ... 
referencing old as t_old
referencing new as t_new
for each row
begin
   ...
   if updating('col1') then
      set t_new.col1 = <whatever>;
   end if;
   if updating('col2') then
      set t_new.col2 = <whatever>;
   end if;
   ...
end;


As a first start, have a look at the "Trigger operation conditions" such as the INSERTING/UPDATING predicates used above...

And note that BEFORE UPDATE triggers always fire whether the values are changed to different values or not whereas AFTER UPDATE triggers only fire if the values are different than before...

permanent link

answered 05 Apr '13, 11:52

Volker%20Barth's gravatar image

Volker Barth
31.5k318461676
accept rate: 33%

edited 05 Apr '13, 11:57

Comment Text Removed

@VolkerBarth, I updated the description of the post :)

I have declared, in the code:

declare Cursor_FIELDS scroll cursor for select syscolumn.column_name from SYSCOLUMN key join sysdomain where TABLE_ID = (select TABLE_ID from SYSTABLE where TABLE_NAME = 'TBCLIENT') order by column_id asc
(05 Apr '13, 14:19) Walmir Taques

If we take your SQL as being specifications or pseudo-code rather than a prototype, there are still a lot of unanswered questions...

What do the tables look like? What are the primary keys? What are the descriptions for all the columns used in the WHERE clauses?

The trigger is updating the SAME TABLE that caused the trigger to fire; is it supposed to update the SAME ROW that caused the trigger to fire, or a different row?

How is the fact the trigger will be fired recursively being handled?

EXECUTE IMMEDIATE and row-level triggers can be used to solve a lot of problems, that's not the issue... what we need to know is what (from the data's point of view) is supposed to happen when a row is updated.

permanent link

answered 07 Apr '13, 08:58

Breck%20Carter's gravatar image

Breck Carter
27.0k444614889
accept rate: 21%

A further question would be if it really matters if an UPDATE statement (issued within the database server itself, not via a client application) does update only a few or all columns of a single row - as this seems the reason you are only interested in updating the "modified" columns. In case it would not matter (which I don't know), I would recommend to use a much easier approach than trying to use EXECUTE IMMEDIATE to generate a "as lean as possible SET list"...

(07 Apr '13, 09:45) Volker Barth

Answering the question of Mr. Carter:

Currently SQL code works to be executed when there TBCLIENT UPDATE table. I'm trying to change that run only for the fields that have changed.

What do the tables look like? Data relating to the registration of a client. What are the primary keys? TBCLIENT -> PK - (EMPRESA, CODIGO) What are the descriptions for all the columns used in the WHERE clauses? EMPRESA = IDEMP CODIGO = IDCLIENT

The trigger is updating the SAME TABLE That Caused the trigger to fire, is it supposed to update the SAME ROW That Caused the trigger to fire, or a different row? Yes, the trigger is updating the same table Caused the trigger to fire. Yes, the same should atualizer line and a line different from the other company (IDEmp) use replication.

I'm trying to change this trigger because already spotted that when you are running a performance database for businesses falls absurdly (IDEmp) that use data replication. This is a test I have to do it may also be that this is not influencing performance. The company to which I refer has about 50 terminals working (querying, inserting, deleting and updating records) and the system after a certain period working in starts getting slow and comes to a stop.

(09 Apr '13, 08:25) Walmir Taques
Replies hidden

Sorry, I guess we are still wondering what exactly you are trying to achieve.

Perhaps it would be easier if you cound show some sample data (just the PK value and a few relevant columns)

  • data before the update
  • the update statement
  • the desired data after the update (including the effects the trigger should do).

Yes, the same should atualizer line and a line different from the other company (IDEmp) use replication.

With "line" are you refering to "row" (i.e. row of that table)? And what does replication mean here - are you using SQL Remote or MobiLink, or do you just mean that another row of the same table has to be updated, too?

(10 Apr '13, 03:54) Volker Barth

I have the following structure example:

    CREATE table TB_TEST (
        emp char(3) not null,
        id INTEGER identity not null,
        field1 char(15) default null,
        field2 char(15) default null,
        field3 char(15) default null
        PRIMARY KEY (emp, id));

    --Here are the inserts for 001 emp

    insert into tb_test (emp, field1, field2, field3) values ('001', 'TEST SEQUENCE 000001',null,null);
    insert into tb_test (emp, field1, field2, field3) values ('001', 'TEST SEQUENCE 000002',null,null);
    insert into tb_test (emp, field1, field2, field3) values ('001', 'TEST SEQUENCE 000003',null,null);

    --Here are the inserts for 002 emp

    insert into tb_test (emp, field1, field2, field3) values ('002', 'TEST SEQUENCE 000001',null,null);
    insert into tb_test (emp, field1, field2, field3) values ('002', 'TEST SEQUENCE 000002',null,null);
    insert into tb_test (emp, field1, field2, field3) values ('002', 'TEST SEQUENCE 000003',null,null);

    create trigger update_tb_test on dba.tb_test
    for update as 
    begin
      declare @NColumns text
      declare @NameColumn varchar(100)
      declare @IDCod integer
      declare @IDEMP char(3)
      declare @Replica char(1)
      declare Cursor_FIELDS scroll cursor for select syscolumn.column_name from SYSCOLUMN key join sysdomain where TABLE_ID = (select TABLE_ID from SYSTABLE where TABLE_NAME = 'TB_TEST') order by column_id asc
      declare Cursor_Replica dynamic scroll cursor for select REPLICA_PRODUTO from TBGEREMPRESA where CODIGO = (select emp from INSERTED)
      open Cursor_Replica
      fetch next Cursor_Replica into @Replica
      if sqlstate = '02000' break
      if @Replica = 'S'
      begin
         --select @IDEmp = inserted.emp
         --select @IDCod = inserted.id
         open Cursor_FIELDS
         while 1=1
         begin
            fetch next Cursor_FIELDS into @NameColumn
            if sqlstate = '02000' break
            if Update(@NameColumn) -- **Update does not work does not show any message**
               set @NColumns=@NCOLUMNS+' '+@NameColumn+'=(select '+@NameColumn+' from inserted)'
         end
         Close Cursor_FIELDS
      end
      execute('UPDATE tb_test SET '+@NCOLUMNS+' where emp='''+@IDEMP+''' and id='''+@IDCod+'''')
    end
--update field1 table tb_test 
--> When upgrading emp 001 
-->must be updated emp 002 (only the changed field)
update TB_TEST SET FIELD1 = 'content of field 1' WHERE EMP='001' and id = 1

I want to write a trigger to update only the fields that have changed in this example the table has only 5 fields. In practice the table that I create this trigger has 200 fields / columns I wrote the following trigger, try to avoid that all fields were entered into the update command, imagine if you change just one field in the table and run to update all fields.

Is not yet solved the problem.

permanent link

answered 12 Sep '13, 18:09

Walmir%20Taques's gravatar image

Walmir Taques
640293147
accept rate: 13%

Hm, could you please show what the result of your trigger should be (as already asked in other comments). It's difficult to help when it's not clear what you are trying to achieve:(

(13 Sep '13, 10:37) Volker Barth

So, when the update is performed in emp 001, for field1 where id equals 1

update tb_test set field1 = 'content field 1' where emp = '001' and id = 1

need to be updated only in the field Field1 line 002 emp, where id is equal to 1.

update tb_test set field1 = 'content field 1' where emp = '002' and id = 1

and is not to be generated sql updating all fields of emp 002, but only the fields that have changed. This is not to be generated:

update tb_test set field1 = 'content field 1', field2 ='', field2 ='' where emp = '002 'and id = 1

I want to avoid is having to enter all the fields, because I want to be included in the sql UPDATE only field whose value has changed.

(13 Sep '13, 15:03) Walmir Taques
Replies hidden

Ah, I see. Just a further question:

Do you want to just include the changed columns in the UPDATE statement on the second row (for emp 002) because

  • you have to assume that the other columns (that have not been modified for emp 001) could have different values for emp 002, so you mustn't overwrite them, or
  • you just want to generate a "short" statement?

In case the second point does apply, I would think that SA handles UPDATES with col1 = col1 quite efficiently.

(13 Sep '13, 16:02) Volker Barth

Yes -->just want to generate a "short" statement? I want to generate only the columns that have changed, to be dealing with a table with many columns and only one field was changed.

Update OF discarded because it should be dynamic, if you insert another column in the table will not have to change the trigger. Understand? Should be dynamic

(13 Sep '13, 16:35) Walmir Taques

if Update(@NameColumn)

Does it work if you replace that with

IF UPDATING(@NameColumn)

since that condition expects a column-name string (instead of an identifier)?

Though I don't know whether this does work in a dynamic SQL statement, and you would need to add a "REFERENCING NEW" clause...

(13 Sep '13, 16:54) Volker Barth
Comment Text Removed

if add the "REFERENCING NEW" clause, the sql will no longer be dynamic.

I've tried to use as well but was not accepted.

if Updating("New".@NameColumn)
(13 Sep '13, 17:19) Walmir Taques

Hm, your sample code does still show some errors, it would be helpful if you could post working code (except for the parts that do not work as expected, obviously...). For example "field1" is a CHAR(15) but your sample data is longer, and your trigger relates to a further table "TBGEREMPRESA"...


If my understanding is correct, you try to check the table's columns within the trigger without naming them explicitly in an attempt to prevent on-going trigger maintenance. Therefore it seems necessary to use a cursor loop over SYSCOLUMN to list all current columns of the according table to find out whether the column

  • has been part of the UPDATE statement's SET CLAUSE (say, be testing "IF UPDATING(col1)") or
  • has been modified (by comparing whether OLD.col1 is distinct from NEW.col1).

But these tests do require that the column name is given as an identifier, and not as a string.

Therefore, I guess these kind of tests would require dynamic SQL (i.e. "EXECUTE ..."), and I guess this won't work for the trigger operation conditions at all, because that test is only valid within the trigger code itself, and not within a separate dynamic query. For the field-level comparison, it should work, however this would require at least one dynamic SQL call per column, and that might be a performance problem.


In case the rows for "emp1" and "emp2" are identical (except for the emp field), would it not be easier to

  • either use INSERT ... ON EXISTING UPDATE WITH AUTO NAME to update all fields
  • or to DELETE the copy and re-insert it by INSERT ... WITH AUTO NAME?

(16 Sep '13, 08:03) Volker Barth
More comments hidden
showing 4 of 7 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:

×102
×61
×31

question asked: 05 Apr '13, 10:32

question was seen: 2,848 times

last updated: 16 Sep '13, 08:05