Re: Trigger for updated rows when PK changed

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: John (jsparrowNOSPAM_at_ecclesdeletethiscollege.ac.uk)
Date: 07/18/04 

Date: Sun, 18 Jul 2004 19:19:36 +0100

I see what you mean!

Maybe updating a pk field should cause delete & insert triggers to be
called, rather than the update trigger?!?

I guess in pure relational theory updating a pk is meaningless - it's always
a delete + insert?

Calling AFTER UPDATE suggests the db knows a physical record was simply
modified (back in base reality where unordered sets don't actually exist!
LOL), even though it's not possible to tell which was altered (without
something like a seperate identify field).

Presumably a row-level trigger in something like Oracle would reflect this
too? 'pk used to be 7, now changed to 17'.

John

"Steve Kass" <skass@drew.edu> wrote in message
news:eKdD0DObEHA.384@TK2MSFTNGP10.phx.gbl...
> John,
>
> The before and after values of what? Unless you have set of columns
> other than the primary key to uniquely the rows of your table, and those
> columns are not updated, you can't make sense of the question. The
> table has some rows before the update, and the table has some rows after
> the update. Who's to say whether they are even the "same rows,"
> whatever that would mean, and which post-update row is the same as which
> pre-update row, if the only way to tell one row from another is by
> reading the primary key value, since the update may have altered any or
> all of those values?
>
> The only way to be able to talk about "this particular row" is if
> "this particular row" means something specific - it has to mean "the row
> with this particular key value". If you need to make sense out of the
> statement "the primary key value of this particular row was changed from
> 101 to 204", there has to be some way to know which row is "this
> particular row" other than by inspecting the value of the primary key.
>
> Suppose that before an update the primary key values in a one-column
> table are 1, 2, and 3. After the update, there are still rows with
> primary key values 2, and 1, but there is no row with key 3, and there
> is a row with key 0. Was the 3 changed to a 0 - and if so, were the 1
> and 2 swapped or not? Or was each number decreased by 1? The query
> processor can have done any of these things. In fact, maybe "the 0" (if
> it meant something) came from another table where a 0 had been deleted
> and was available for reuse, and maybe "the 3" got used by another
> program that needed one.
>
> Steve Kass
> Drew University
>
>
> John wrote:
>
> >Does anyone know of a way to discover before and after values in a AFTER
> >UPDATE trigger when the primary key has changed?? I need this to work
with
> >multi-rows too!
> >
> >I'm familiar with the 'inserted i inner join deleted d on i.pk = d.pk'
> >technique for seeing 'before' and 'after' values, but of course this
doesn't
> >work when the pk is changed.
> >
> >Help!
> >
> >I guess the only solution is to ensure pk field(s) are never altered?
> >
> >John
> >
> >
> >
> >

Relevant Pages

  • Re: Help with INSERT TRIGGER - fails with error.
    ... You didn't declare any PRIMARY KEY in this database. ... In an INSERT trigger, ... but I can't get it to work because it references ntext fields. ...
    (comp.databases.ms-sqlserver)
  • Re: Whats wrong with this trigger
    ... the app that uses this data is .NET. ... > same values for the primary key as the ones used in the original tables. ... >>>update trigger that inserts an audit row into a view. ... >>>identity and so is the view that I'm inserting the audit row into. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Trigger
    ... CREATE TRIGGER MyTrigger ... -- Here is the first calculation. ... columns and make sure you use the entire primary key in the EXISTS ... Make sure your sample data illustrates what you ...
    (microsoft.public.sqlserver.programming)
  • Re: Triggers - Inserted and Deleted tables cross join?
    ... > I am creating a trigger that runs for update on table X that then updates ... > The user needs to be able to change the primary key on table X and the old ... There is no guarantee about the order of records in INSERTED and DELETED, ... is no way to correlate the rows in INSERTED and DELETED. ...
    (microsoft.public.sqlserver.programming)
  • Re: my access many to many design will not let me add records
    ... John W. Vinson ... Name Primary Key ... I have a form that I made with the wizard to enter records. ... primary Key in the HighPoint table. ...
    (microsoft.public.access.tablesdbdesign)