Trigger question

Trigger question

Post by Eri » Sat, 18 Dec 1999 04:00:00



I have an update trigger on a table (table A) that a column in the
table to see if it has changed.  If the column has changed I need to
update the same column in another table (Table B).  This works except
if I issue an update that updates several rows.  In this case the way
the trigger is setup it only fires for one of the rows that was
updated in table A.  So do I need to use a cursor in the table and
cycle through the inserted table and update Table B that way?  Or is
there a better to ensure that for each row updated in Table A the
trigger fires?

Thanks

 
 
 

Trigger question

Post by Kalen Delane » Sat, 18 Dec 1999 04:00:00


If the column you are changing is the primary key, you'll have a problem
matching old values with new, regardless of whether you use cursors or not.
You should try to avoid cursors if possible, since they are notoriously
slow.

What determines which rows in tableB should change? If tableA is the
referenced table with a PrimaryKey, and tableB is the referencing table with
the Foreign Key, you could try something like this:

create trigger mytrig
on tableA
for update
as

if update(mycolumn)
    update  tableB
         set tableB.mycolumn = inserted.mycolumn
         from inserted, tableB
        where inserted.PrimaryKeyColumn = tableB.ForeignKeyColumn

HTH

--
Kalen Delaney
MCSE, SQL Server MCT, MVP
www.InsideSQLServer.com
Feed Someone for Free Today:
     www.TheHungerSite.com


Quote:> I have an update trigger on a table (table A) that a column in the
> table to see if it has changed.  If the column has changed I need to
> update the same column in another table (Table B).  This works except
> if I issue an update that updates several rows.  In this case the way
> the trigger is setup it only fires for one of the rows that was
> updated in table A.  So do I need to use a cursor in the table and
> cycle through the inserted table and update Table B that way?  Or is
> there a better to ensure that for each row updated in Table A the
> trigger fires?

> Thanks


 
 
 

Trigger question

Post by Eri » Sat, 18 Dec 1999 04:00:00


Kalen,
        Thanks for the reply.  I should have elaborated a little more.
Neither of the colummns in Table A or B are primary keys.

Here is a better example I hope::)

Table A

KEYFIELD char(10) primary key
WORKAREA char(10)

Table B

KEYFIELD char(10) primary key
TABLE_A_KEYFIELD char(10)
WORKAREA char(10)

suppose I update several records in Table A changing the value of
workarea.  

update tableA set workarea = '12345' where workarea = '000000'

This change needs to be reflected in every matching Table B workarea
column.  Table B may have mulitple records whos table_a_keyfield =
table A KEYFIELD.  As my trigger is now only one record from TABLE A
has the trigger fired given the above query.

Hope this is clearer

Thanks

On Fri, 17 Dec 1999 09:53:31 -0800, "Kalen Delaney"


>If the column you are changing is the primary key, you'll have a problem
>matching old values with new, regardless of whether you use cursors or not.
>You should try to avoid cursors if possible, since they are notoriously
>slow.

>What determines which rows in tableB should change? If tableA is the
>referenced table with a PrimaryKey, and tableB is the referencing table with
>the Foreign Key, you could try something like this:

>create trigger mytrig
>on tableA
>for update
>as

>if update(mycolumn)
>    update  tableB
>         set tableB.mycolumn = inserted.mycolumn
>         from inserted, tableB
>        where inserted.PrimaryKeyColumn = tableB.ForeignKeyColumn

>HTH

>--
>Kalen Delaney
>MCSE, SQL Server MCT, MVP
>www.InsideSQLServer.com
>Feed Someone for Free Today:
>     www.TheHungerSite.com



>> I have an update trigger on a table (table A) that a column in the
>> table to see if it has changed.  If the column has changed I need to
>> update the same column in another table (Table B).  This works except
>> if I issue an update that updates several rows.  In this case the way
>> the trigger is setup it only fires for one of the rows that was
>> updated in table A.  So do I need to use a cursor in the table and
>> cycle through the inserted table and update Table B that way?  Or is
>> there a better to ensure that for each row updated in Table A the
>> trigger fires?

>> Thanks

 
 
 

Trigger question

Post by Kalen Delane » Sat, 18 Dec 1999 04:00:00


So since you are not updating Primary Key fields, did you try my original
suggestion?

--
Kalen Delaney
MCSE, SQL Server MCT, MVP
www.InsideSQLServer.com
Feed Someone for Free Today:
     www.TheHungerSite.com


> Kalen,
> Thanks for the reply.  I should have elaborated a little more.
> Neither of the colummns in Table A or B are primary keys.

> Here is a better example I hope::)

> Table A

> KEYFIELD char(10) primary key
> WORKAREA char(10)

> Table B

> KEYFIELD char(10) primary key
> TABLE_A_KEYFIELD char(10)
> WORKAREA char(10)

> suppose I update several records in Table A changing the value of
> workarea.

> update tableA set workarea = '12345' where workarea = '000000'

> This change needs to be reflected in every matching Table B workarea
> column.  Table B may have mulitple records whos table_a_keyfield =
> table A KEYFIELD.  As my trigger is now only one record from TABLE A
> has the trigger fired given the above query.

> Hope this is clearer

> Thanks

> On Fri, 17 Dec 1999 09:53:31 -0800, "Kalen Delaney"

> >If the column you are changing is the primary key, you'll have a problem
> >matching old values with new, regardless of whether you use cursors or
not.
> >You should try to avoid cursors if possible, since they are notoriously
> >slow.

> >What determines which rows in tableB should change? If tableA is the
> >referenced table with a PrimaryKey, and tableB is the referencing table
with
> >the Foreign Key, you could try something like this:

> >create trigger mytrig
> >on tableA
> >for update
> >as

> >if update(mycolumn)
> >    update  tableB
> >         set tableB.mycolumn = inserted.mycolumn
> >         from inserted, tableB
> >        where inserted.PrimaryKeyColumn = tableB.ForeignKeyColumn

> >HTH

> >--
> >Kalen Delaney
> >MCSE, SQL Server MCT, MVP
> >www.InsideSQLServer.com
> >Feed Someone for Free Today:
> >     www.TheHungerSite.com



> >> I have an update trigger on a table (table A) that a column in the
> >> table to see if it has changed.  If the column has changed I need to
> >> update the same column in another table (Table B).  This works except
> >> if I issue an update that updates several rows.  In this case the way
> >> the trigger is setup it only fires for one of the rows that was
> >> updated in table A.  So do I need to use a cursor in the table and
> >> cycle through the inserted table and update Table B that way?  Or is
> >> there a better to ensure that for each row updated in Table A the
> >> trigger fires?

> >> Thanks

 
 
 

Trigger question

Post by Roy Harve » Sat, 18 Dec 1999 04:00:00


Eric,

How about something like:

  UPDATE B
     SET B.workarea = I.workarea
    FROM Inserted as I,
         Deleted as D
   WHERE I.keyfield = D.keyfield
     AND I.workarea <> D.workarea
     and I.keyfield = B.tablea_a_keyfield

Roy


>Kalen,
>    Thanks for the reply.  I should have elaborated a little more.
>Neither of the colummns in Table A or B are primary keys.

>Here is a better example I hope::)

>Table A

>KEYFIELD char(10) primary key
>WORKAREA char(10)

>Table B

>KEYFIELD char(10) primary key
>TABLE_A_KEYFIELD char(10)
>WORKAREA char(10)

>suppose I update several records in Table A changing the value of
>workarea.  

>update tableA set workarea = '12345' where workarea = '000000'

>This change needs to be reflected in every matching Table B workarea
>column.  Table B may have mulitple records whos table_a_keyfield =
>table A KEYFIELD.  As my trigger is now only one record from TABLE A
>has the trigger fired given the above query.

>Hope this is clearer

>Thanks

>On Fri, 17 Dec 1999 09:53:31 -0800, "Kalen Delaney"

>>If the column you are changing is the primary key, you'll have a problem
>>matching old values with new, regardless of whether you use cursors or not.
>>You should try to avoid cursors if possible, since they are notoriously
>>slow.

>>What determines which rows in tableB should change? If tableA is the
>>referenced table with a PrimaryKey, and tableB is the referencing table with
>>the Foreign Key, you could try something like this:

>>create trigger mytrig
>>on tableA
>>for update
>>as

>>if update(mycolumn)
>>    update  tableB
>>         set tableB.mycolumn = inserted.mycolumn
>>         from inserted, tableB
>>        where inserted.PrimaryKeyColumn = tableB.ForeignKeyColumn

>>HTH

>>--
>>Kalen Delaney
>>MCSE, SQL Server MCT, MVP
>>www.InsideSQLServer.com
>>Feed Someone for Free Today:
>>     www.TheHungerSite.com



>>> I have an update trigger on a table (table A) that a column in the
>>> table to see if it has changed.  If the column has changed I need to
>>> update the same column in another table (Table B).  This works except
>>> if I issue an update that updates several rows.  In this case the way
>>> the trigger is setup it only fires for one of the rows that was
>>> updated in table A.  So do I need to use a cursor in the table and
>>> cycle through the inserted table and update Table B that way?  Or is
>>> there a better to ensure that for each row updated in Table A the
>>> trigger fires?

>>> Thanks

 
 
 

1. Default value and Trigger question ( implement before trigger)

Hello, theres
   I have trouble to set default value for column.
   my problem as follows:

   create table test ( c1 int, c2 char(1), c3 int)
   The rule is that c3 is depend on c1 and c2.

   How do I set the c3 with the default value with ComputeC3(c1,c2) ??

   I try to use a trigger to do this, but doesn't work.
   how to update inserted table in trigger (implement/simulate)??

Rgds,
Agi

2. Item Count becomes by itself

3. Newbie trigger question.

4. Master Merge failure

5. Trigger Question

6. OPTIMIZER CHOOSE vs. FIRST_ROWS

7. IF Update in After Trigger, questions

8. INVENTORY CONTROL SYSTEMS RESEARCH, HELP ME

9. Basic trigger question

10. Trigger Question

11. SQL Trigger Question

12. Trigger Question