Professional Web Applications Themes

Using Inserted and Deleted tables in Conjunction in a Trigger - Microsoft SQL / MS SQL Server

I want to check a table to see if a column (req_ship_dt) has been updated with an update trigger using the following: IF UPDATE (req_ship_dt) I discovered that our accounting system will delete and insert a row with no change made to req_ship_dt, but change other columns during a shipping process. This is enough to set off the trigger. I attempted the following: IF (SELECT req_ship_dt FROM INSERTED) <> (SELECT req_ship_dt FROM DELETED) AND UPDATE (req_ship_dt) I hoped that by checking for a difference between the Inserted and Deleted tables for that column that I would eliminate the shipping process ...

  1. #1

    Default Using Inserted and Deleted tables in Conjunction in a Trigger

    I want to check a table to see if a column (req_ship_dt) has been updated
    with an update trigger using the following:

    IF UPDATE (req_ship_dt)

    I discovered that our accounting system will delete and insert a row with no
    change made to req_ship_dt, but change other columns during a shipping
    process. This is enough to set off the trigger. I attempted the following:

    IF (SELECT req_ship_dt FROM INSERTED) <> (SELECT req_ship_dt FROM DELETED)
    AND UPDATE (req_ship_dt)

    I hoped that by checking for a difference between the Inserted and Deleted
    tables for that column that I would eliminate the shipping process from
    setting off the trigger. Here is the error message that I received:

    Server: Msg 512, Level 16, State 1, Procedure ChangeOrdDateOEORDLINTrig,
    Line 6
    Subquery returned more than 1 value. This is not permitted when the subquery
    follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
    The statement has been terminated.

    What is a valid way of checking for a change in a column under these
    cirstances?

    Thanks in advance.

    --
    Mark Simmerman
    SQL Learner
    Napa, CA, USA


    Mark Simmerman Guest

  2. #2

    Default Re: Using Inserted and Deleted tables in Conjunction in a Trigger

    Steve,

    The accounting system that we use is not very normalized. There is not
    primary key for the table (OrdDetail). The table has an id column
    (A4glidentity) with an identity set, and that column is part of a unique
    index. If it has to be a primary key, is there another workable method?

    Another challenge is that the shipping process that deletes and inserts
    the same row to change a value in an inconsequential column (status), does
    not change the value in A4glidentity.

    Will your method work here? If not, what else can I try?

    Thanks,
    Mark


    "Steve Kass" <skassdrew.edu> wrote in message
    news:OcYS7UYRDHA.704tk2msftngp13.phx.gbl...
    > Mark,
    >
    > You need to refer to a primary key column, as in
    >
    > if exists (
    > select * from inserted, deleted
    > where inserted.primarykeycolumn = deleted.primarykeycolumn
    > and inserted.req_ship_dt <> deleted.req_ship_dt
    > )
    > ...
    >
    > Steve Kass
    > Drew University
    >
    > Mark Simmerman wrote:
    >
    > >I want to check a table to see if a column (req_ship_dt) has been
    updated
    > >with an update trigger using the following:
    > >
    > >IF UPDATE (req_ship_dt)
    > >
    > >I discovered that our accounting system will delete and insert a row with
    no
    > >change made to req_ship_dt, but change other columns during a shipping
    > >process. This is enough to set off the trigger. I attempted the
    following:
    > >
    > >IF (SELECT req_ship_dt FROM INSERTED) <> (SELECT req_ship_dt FROM
    DELETED)
    > >AND UPDATE (req_ship_dt)
    > >
    > >I hoped that by checking for a difference between the Inserted and
    Deleted
    > >tables for that column that I would eliminate the shipping process from
    > >setting off the trigger. Here is the error message that I received:
    > >
    > >Server: Msg 512, Level 16, State 1, Procedure ChangeOrdDateOEORDLINTrig,
    > >Line 6
    > >Subquery returned more than 1 value. This is not permitted when the
    subquery
    > >follows =, !=, <, <= , >, >= or when the subquery is used as an
    expression.
    > >The statement has been terminated.
    > >
    > >What is a valid way of checking for a change in a column under these
    > >cirstances?
    > >
    > >Thanks in advance.
    > >
    > >
    > >
    >

    Mark Simmerman Guest

  3. #3

    Default Re: Using Inserted and Deleted tables in Conjunction in a Trigger

    Mark,

    A unique index will do. The point is that you can't know
    whether req_ship_dt has been changed unless you know how
    to match up the rows in inserted with the rows in deleted. If the
    unique index has more than one column, it will just be

    if exists (
    select * from inserted, deleted
    where inserted.UniqueIndexColumn1 = deleted.UniqueIndexColumn1
    and inserted.UniqueIndexColumn2 = deleted.UniqueIndexColumn2
    ...
    and inserted.req_ship_dt <> deleted.req_ship_dt
    )

    If I've misunderstood and this doesn't help, post the table structures
    and an example of the kind of change you want to catch or ignore.

    Steve

    Mark Simmerman wrote:
    >Steve,
    >
    > The accounting system that we use is not very normalized. There is not
    >primary key for the table (OrdDetail). The table has an id column
    >(A4glidentity) with an identity set, and that column is part of a unique
    >index. If it has to be a primary key, is there another workable method?
    >
    > Another challenge is that the shipping process that deletes and inserts
    >the same row to change a value in an inconsequential column (status), does
    >not change the value in A4glidentity.
    >
    >Will your method work here? If not, what else can I try?
    >
    >Thanks,
    >Mark
    >
    >
    >"Steve Kass" <skassdrew.edu> wrote in message
    >news:OcYS7UYRDHA.704tk2msftngp13.phx.gbl...
    >
    >
    >>Mark,
    >>
    >> You need to refer to a primary key column, as in
    >>
    >>if exists (
    >> select * from inserted, deleted
    >> where inserted.primarykeycolumn = deleted.primarykeycolumn
    >> and inserted.req_ship_dt <> deleted.req_ship_dt
    >>)
    >>...
    >>
    >>Steve Kass
    >>Drew University
    >>
    >>Mark Simmerman wrote:
    >>
    >>
    >>
    >>>I want to check a table to see if a column (req_ship_dt) has been
    >>>
    >>>
    >updated
    >
    >
    >>>with an update trigger using the following:
    >>>
    >>>IF UPDATE (req_ship_dt)
    >>>
    >>>I discovered that our accounting system will delete and insert a row with
    >>>
    >>>
    >no
    >
    >
    >>>change made to req_ship_dt, but change other columns during a shipping
    >>>process. This is enough to set off the trigger. I attempted the
    >>>
    >>>
    >following:
    >
    >
    >>>IF (SELECT req_ship_dt FROM INSERTED) <> (SELECT req_ship_dt FROM
    >>>
    >>>
    >DELETED)
    >
    >
    >>>AND UPDATE (req_ship_dt)
    >>>
    >>>I hoped that by checking for a difference between the Inserted and
    >>>
    >>>
    >Deleted
    >
    >
    >>>tables for that column that I would eliminate the shipping process from
    >>>setting off the trigger. Here is the error message that I received:
    >>>
    >>>Server: Msg 512, Level 16, State 1, Procedure ChangeOrdDateOEORDLINTrig,
    >>>Line 6
    >>>Subquery returned more than 1 value. This is not permitted when the
    >>>
    >>>
    >subquery
    >
    >
    >>>follows =, !=, <, <= , >, >= or when the subquery is used as an
    >>>
    >>>
    >expression.
    >
    >
    >>>The statement has been terminated.
    >>>
    >>>What is a valid way of checking for a change in a column under these
    >>>cirstances?
    >>>
    >>>Thanks in advance.
    >>>
    >>>
    >>>
    >>>
    >>>
    >
    >
    >
    >
    Steve Kass Guest

  4. #4

    Default Using Inserted and Deleted tables in Conjunction in a Trigger

    I had a similar problem with our system.

    But our frontend appliction only affects one row on every
    update so I solved it by adding "IF ROWCOUNT <> 1
    RETURN" at the beginning of the update trigger.

    It works fine as long you don't try to update more than
    one row. If more than one row is affected by the update
    the trigger will never fire.

    /Tobbe


    >-----Original Message-----
    >I want to check a table to see if a column (req_ship_dt)
    has been updated
    >with an update trigger using the following:
    >
    >IF UPDATE (req_ship_dt)
    >
    >I discovered that our accounting system will delete and
    insert a row with no
    >change made to req_ship_dt, but change other columns
    during a shipping
    >process. This is enough to set off the trigger. I
    attempted the following:
    >
    >IF (SELECT req_ship_dt FROM INSERTED) <> (SELECT
    req_ship_dt FROM DELETED)
    >AND UPDATE (req_ship_dt)
    >
    >I hoped that by checking for a difference between the
    Inserted and Deleted
    >tables for that column that I would eliminate the
    shipping process from
    >setting off the trigger. Here is the error message that
    I received:
    >
    >Server: Msg 512, Level 16, State 1, Procedure
    ChangeOrdDateOEORDLINTrig,
    >Line 6
    >Subquery returned more than 1 value. This is not
    permitted when the subquery
    >follows =, !=, <, <= , >, >= or when the subquery is used
    as an expression.
    >The statement has been terminated.
    >
    >What is a valid way of checking for a change in a column
    under these
    >cirstances?
    >
    >Thanks in advance.
    >
    >--
    >Mark Simmerman
    >SQL Learner
    >Napa, CA, USA
    >
    >
    >.
    >
    Tobbe Guest

Similar Threads

  1. Trigger: what rows were deleted?
    By Ciprian Popovici in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: December 17th, 02:00 PM
  2. Replies: 2
    Last Post: September 9th, 12:07 PM
  3. trigger transition tables
    By alederer in forum IBM DB2
    Replies: 1
    Last Post: August 14th, 03:30 PM
  4. Question on triggers and special 'deleted' 'inserted' tables
    By Zig Mandel in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 9th, 10:04 PM
  5. note 33601 deleted from function.trigger-error by alindeman
    By alindeman@php.net in forum PHP Notes
    Replies: 0
    Last Post: July 1st, 06:11 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139