Update Trigger - previous values

Update Trigger - previous values

Post by Mark Howel » Fri, 19 Nov 1999 04:00:00



I have an UPDATE trigger and am using the DELETED table.  However, how do I
match up the record in the DELETED table with the record in the table being
updated.  The purpose is to make cascading updates.  To do so, I need the
value being changed from and the value that it changed to.  My understanding
is that the DELETED table contains all the rows that were changed.   How do
I match the rows back up?
 
 
 

Update Trigger - previous values

Post by Tony Rogerso » Fri, 19 Nov 1999 04:00:00


You join the deleted table against the base table being updated using a key
that uniquely identify the records.

deleted table contains old row
inserted and base table contains new row

--
Tony Rogerson SQL Server MVP
www.sql-server.co.uk [ Independent SQL Server home page -
    Tutorials,
    Code library,
    FAQ,
    UK user group, discussions and chat.
]


Quote:> I have an UPDATE trigger and am using the DELETED table.  However, how do
I
> match up the record in the DELETED table with the record in the table
being
> updated.  The purpose is to make cascading updates.  To do so, I need the
> value being changed from and the value that it changed to.  My
understanding
> is that the DELETED table contains all the rows that were changed.   How
do
> I match the rows back up?


 
 
 

Update Trigger - previous values

Post by Mark Howel » Fri, 19 Nov 1999 04:00:00


The problem is that it is that unique key that I'm modifying.  (Yes, I know
that it is a bad thing, but project and timing constraints, dictate that it
is currently necessary).   Joining on other fields would be easy if there
were another field that made it unique.  So, how else would I be able to
link the rows together?

>You join the deleted table against the base table being updated using a key
>that uniquely identify the records.

>deleted table contains old row
>inserted and base table contains new row

 
 
 

Update Trigger - previous values

Post by Tony Rogerso » Fri, 19 Nov 1999 04:00:00


This is difficult !

You could use a cursor on inserted and deleted to scroll through the
inserted table..

declare insrecs_cur cursor for
    select ikey = i.key
    from inserted i cross join deleted d

declare delrecs_cur cursor for
    select ikey = i.key
    from inserted i cross join deleted d

open ....

fetch insrecs
fetch delrecs

You can then determine the old key value and the new key value for the
record.

--
Tony Rogerson SQL Server MVP
www.sql-server.co.uk [ Independent SQL Server home page -
    Tutorials,
    Code library,
    FAQ,
    UK user group, discussions and chat.
]


> The problem is that it is that unique key that I'm modifying.  (Yes, I
know
> that it is a bad thing, but project and timing constraints, dictate that
it
> is currently necessary).   Joining on other fields would be easy if there
> were another field that made it unique.  So, how else would I be able to
> link the rows together?


> >You join the deleted table against the base table being updated using a
key
> >that uniquely identify the records.

> >deleted table contains old row
> >inserted and base table contains new row

 
 
 

Update Trigger - previous values

Post by Tibor Karasz » Sat, 20 Nov 1999 04:00:00


Tony,

Is your cursor based on "row ordering" (I couldn't determine that for sure)?

If so, note that "row ordering" is not guaranteed in INSERTED and DELETED
trigger tables in SQL Server (7?).

I don't know in what situations the rows won't be in the same order, but as
usual that is determined by the execution path taken by SQL Server.
--
Tibor Karaszi
MCDBA, MCSE, MCSD, MCT, SQL Server MVP
Cornerstone Sweden AB
Please reply to the newsgroup only, not by email.


> This is difficult !

> You could use a cursor on inserted and deleted to scroll through the
> inserted table..

> declare insrecs_cur cursor for
>     select ikey = i.key
>     from inserted i cross join deleted d

> declare delrecs_cur cursor for
>     select ikey = i.key
>     from inserted i cross join deleted d

> open ....

> fetch insrecs
> fetch delrecs

> You can then determine the old key value and the new key value for the
> record.

> --
> Tony Rogerson SQL Server MVP
> www.sql-server.co.uk [ Independent SQL Server home page -
>     Tutorials,
>     Code library,
>     FAQ,
>     UK user group, discussions and chat.
> ]



> > The problem is that it is that unique key that I'm modifying.  (Yes, I
> know
> > that it is a bad thing, but project and timing constraints, dictate that
> it
> > is currently necessary).   Joining on other fields would be easy if
there
> > were another field that made it unique.  So, how else would I be able to
> > link the rows together?


> > >You join the deleted table against the base table being updated using a
> key
> > >that uniquely identify the records.

> > >deleted table contains old row
> > >inserted and base table contains new row

 
 
 

Update Trigger - previous values

Post by Tony Rogerso » Tue, 23 Nov 1999 04:00:00


Based on row ordering.

It's my experience that they are.

The inserted and deleted tables are just representations (pointers) to
what's on the log.

--
Tony Rogerson SQL Server MVP
Torver Computer Consultants Ltd.

www.sql-server.co.uk
(FAQ;code library;tutorials;UK User Group)


> Tony,

> Is your cursor based on "row ordering" (I couldn't determine that for
sure)?

> If so, note that "row ordering" is not guaranteed in INSERTED and DELETED
> trigger tables in SQL Server (7?).

> I don't know in what situations the rows won't be in the same order, but
as
> usual that is determined by the execution path taken by SQL Server.
> --
> Tibor Karaszi
> MCDBA, MCSE, MCSD, MCT, SQL Server MVP
> Cornerstone Sweden AB
> Please reply to the newsgroup only, not by email.



> > This is difficult !

> > You could use a cursor on inserted and deleted to scroll through the
> > inserted table..

> > declare insrecs_cur cursor for
> >     select ikey = i.key
> >     from inserted i cross join deleted d

> > declare delrecs_cur cursor for
> >     select ikey = i.key
> >     from inserted i cross join deleted d

> > open ....

> > fetch insrecs
> > fetch delrecs

> > You can then determine the old key value and the new key value for the
> > record.

> > --
> > Tony Rogerson SQL Server MVP
> > www.sql-server.co.uk [ Independent SQL Server home page -
> >     Tutorials,
> >     Code library,
> >     FAQ,
> >     UK user group, discussions and chat.
> > ]



> > > The problem is that it is that unique key that I'm modifying.  (Yes, I
> > know
> > > that it is a bad thing, but project and timing constraints, dictate
that
> > it
> > > is currently necessary).   Joining on other fields would be easy if
> there
> > > were another field that made it unique.  So, how else would I be able
to
> > > link the rows together?


> > > >You join the deleted table against the base table being updated using
a
> > key
> > > >that uniquely identify the records.

> > > >deleted table contains old row
> > > >inserted and base table contains new row

 
 
 

Update Trigger - previous values

Post by Tibor Karasz » Tue, 23 Nov 1999 04:00:00


Yes, I suppose that in vast number of cases it will work.
I have, however, heard that in 7, that might now be true (again, in all
cases)....

Hmm, cant seem to think of any example why rows (log entries) could be
represented out of sequence, right now...
--
Tibor


> Based on row ordering.

> It's my experience that they are.

> The inserted and deleted tables are just representations (pointers) to
> what's on the log.

> --
> Tony Rogerson SQL Server MVP
> Torver Computer Consultants Ltd.

> www.sql-server.co.uk
> (FAQ;code library;tutorials;UK User Group)



> > Tony,

> > Is your cursor based on "row ordering" (I couldn't determine that for
> sure)?

> > If so, note that "row ordering" is not guaranteed in INSERTED and
DELETED
> > trigger tables in SQL Server (7?).

> > I don't know in what situations the rows won't be in the same order, but
> as
> > usual that is determined by the execution path taken by SQL Server.
> > --
> > Tibor Karaszi
> > MCDBA, MCSE, MCSD, MCT, SQL Server MVP
> > Cornerstone Sweden AB
> > Please reply to the newsgroup only, not by email.



> > > This is difficult !

> > > You could use a cursor on inserted and deleted to scroll through the
> > > inserted table..

> > > declare insrecs_cur cursor for
> > >     select ikey = i.key
> > >     from inserted i cross join deleted d

> > > declare delrecs_cur cursor for
> > >     select ikey = i.key
> > >     from inserted i cross join deleted d

> > > open ....

> > > fetch insrecs
> > > fetch delrecs

> > > You can then determine the old key value and the new key value for the
> > > record.

> > > --
> > > Tony Rogerson SQL Server MVP
> > > www.sql-server.co.uk [ Independent SQL Server home page -
> > >     Tutorials,
> > >     Code library,
> > >     FAQ,
> > >     UK user group, discussions and chat.
> > > ]



> > > > The problem is that it is that unique key that I'm modifying.  (Yes,
I
> > > know
> > > > that it is a bad thing, but project and timing constraints, dictate
> that
> > > it
> > > > is currently necessary).   Joining on other fields would be easy if
> > there
> > > > were another field that made it unique.  So, how else would I be
able
> to
> > > > link the rows together?


> > > > >You join the deleted table against the base table being updated
using
> a
> > > key
> > > > >that uniquely identify the records.

> > > > >deleted table contains old row
> > > > >inserted and base table contains new row

 
 
 

1. trigger previous values

Hi everyone,
     I have a question about triggers in sql server 7.
When a record is updated in the database, i would like to write the old
value of a column in to a separate table. example

create table people(
        name varchar(20)
)

create table people_history(
        oldname varchar(20),
        newname varchar(20)
)

insert into people(name) values('eric')
update people set (name = 'eric changed')
select * from people_history

and this should return the following:
oldname         newname
________________________________
eric            eric changed

when creating the trigger both the "inserted" table and "people" table
would have the value 'eric changed' in them when
accessed from within the trigger. how do i determine what the old value
of a column was???

if you have any difficulty understanding what i'm trying to do, please
just ask.

Thanks,
Eric

Sent via Deja.com http://www.deja.com/
Before you buy.

2. Version Number (???)

3. UPDATE Using Previous Record Value

4. SQL Server Inserts very slow on multi-processor server

5. Updating table that contain previous values

6. Stored Procedures Request

7. Help! Updating table that contain previous values

8. Update a record using a previous records value

9. update trigger > reuse values in update clause

10. counting record only when field value is not equal to value of previous record

11. Update reverses to a previous record before the update

12. select value then update value without intervening reads/updates