|
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 |
|
Could you be more specific:
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... 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. 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)
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. 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
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
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
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
(16 Sep '13, 08:03)
Volker Barth
More comments hidden
|