Very Computer

Hi,

First of all sorry for this looong question...

I have two tables
1: CMENVI - with data changes
2: BAS_EMPRESA  - data table

On BAS_EMPRESA I have the following triggers on insert, update.

CREATE TRIGGER PSNI_BAS_EMPRESA ON BAS_EMPRESA
    FOR  INSERT, UPDATE
   AS



  IF (SYSTEM_USER NOT LIKE 'REPL%') BEGIN

     INSERT INTO CMENVI ( dCDA, iSEQ, iNCO, sTBA, sCLA, sDEL, sICE,

       IF UPDATE(ESNOME) BEGIN     INSERT INTO CMENVI ( dCDA, iSEQ,
iNCO, sTBA, sCLA, sDEL, sICE,    CT_char)    SELECT CONVERT(DATETIME,

       IF UPDATE(ESRAZAO) BEGIN     INSERT INTO CMENVI ( dCDA, iSEQ,
iNCO, sTBA, sCLA, sDEL, sICE,    CT_char)    SELECT CONVERT(DATETIME,

END

After updated record, it should insert the changed data into table
CMENVI, but only its primary key and the one changed. What this trigger
do ..
On column iseq goes the same number for all columns that have changed on
the same record. If all 3 columns were changed (for example) I must have
CMENVI like this if I update  registries.

Data        Seq.    Nr Cols.  Table      Column     --    --       Value

dCDA     -  iSEQ - iNCO -    sTBA     - sCLA     - sDEL - sICE - CT_CHAR
01/01/01 -    1      3    BAS_EMPRESA  - 'ESESTAB'  - 'N' -  'N' - '001'
01/01/01 -    1      3    BAS_EMPRESA  - 'ESNOME'  - 'N' -  'N' -  'T1'
01/01/01 -    1      3    BAS_EMPRESA  - 'ESRAZAO'  - 'N' - 'N' -  'TR1'

01/01/01 -    2      3    BAS_EMPRESA  - 'ESESTAB'  - 'N' -  'N' - '002'
01/01/01 -    2      3    BAS_EMPRESA  - 'ESNOME'  - 'N' -  'N' -  'T2'
01/01/01 -    2      3    BAS_EMPRESA  - 'ESRAZAO'  - 'N' - 'N' -  'TR2'

If I update registry by registry I have no problem, but if I do
something like Update BAS_EMRPESA set ESNOME = ESNOME,  to update all
registries I get:
Data        Seq.    Nr Cols.  Table      Column     --    --       Value

dCDA     -  iSEQ - iNCO -    sTBA     - sCLA     - sDEL - sICE - CT_CHAR
01/01/01 -    1      3    BAS_EMPRESA  - 'ESESTAB'  - 'N' -  'N' - '001'
01/01/01 -    1      3    BAS_EMPRESA  - 'ESNOME'  - 'N' -  'N' -  'T1'
01/01/01 -    1      3    BAS_EMPRESA  - 'ESRAZAO'  - 'N' - 'N' -  'TR1'

01/01/01 -    1      3    BAS_EMPRESA  - 'ESESTAB'  - 'N' -  'N' - '002'
01/01/01 -    1      3    BAS_EMPRESA  - 'ESNOME'  - 'N' -  'N' -  'T2'
01/01/01 -    1      3    BAS_EMPRESA  - 'ESRAZAO'  - 'N' - 'N' -  'TR2'

The two record with the same sequence.

Is there any way to do this? I'm getting bold here.. :)

Thanks in advance for any ligth!

Ricardo Costa
Florianpolis - SC - Brazil

I saw that when the trigger inserts tha values to other table, first it
get all the first record, then the second and so on (If I make a
complete update in the table (UPDATE TABLE SET COL = COL, for example)
Is there a way to tell the trigger that it must first complete cicle and
then go on to the next one?

Thanks in advance

------------------------------
Ricardo Costa
IONICS Technology
Florianopolis - SC - Brasil

The reason for your problem is that the trigger is only executed once even
when you update multiple rows.

When updating multiple rows, the INSERTED table will contain multiple rows.

same value for all inserted rows.

I think that you will have to create a cursor and loop through the inserted
table record by record.

/SG

mean, I get this variable to insert records on other table.. Even so
this variable is on inserted table?

Thanks Stefan for the help

Ricardo Costa