Hello all!
I was wondering if, within a trigger that's defined FOR INSERT, UPDATE, can it determine
whether the action that fired the trigger was an INSERT versus an UPDATE? I've got a
little "extra" logic to do for an UPDATE that I want to avoid with an INSERT. Or do I
need to have two separate triggers, and then potentially call out to a third SP that has
the "guts" of the current trigger?
Thanks!
John PetersonThis is a multi-part message in MIME format.
--=_NextPart_000_04DA_01C3B8E9.27FD2C60
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
If your trigger handles only inserts and updates, then you will have rows in
inserted but not in deleted when there is an insert. However, if there is
an update, then both will be populated:
if @.@.ROWCOUNT = 0
return
if exists (select * from deleted)
begin
-- do the update processing
end
else
begin
-- do the insert processing
end
go
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:uK1T8IRuDHA.3436@.tk2msftngp13.phx.gbl...
(SQL Server 2000, SP3a)
Hello all!
I was wondering if, within a trigger that's defined FOR INSERT, UPDATE, can
it determine
whether the action that fired the trigger was an INSERT versus an UPDATE?
I've got a
little "extra" logic to do for an UPDATE that I want to avoid with an
INSERT. Or do I
need to have two separate triggers, and then potentially call out to a third
SP that has
the "guts" of the current trigger?
Thanks!
John Peterson
--=_NextPart_000_04DA_01C3B8E9.27FD2C60
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
If your trigger handles only inserts =and updates, then you will have rows in inserted but not in deleted when there is an insert. However, if there is an update, then both will be populated:
if @.@.ROWCOUNT =3D =0
=return
if exists (select * from deleted)
begin
-- do the =update processing
end
else
begin
-- do the =insert processing
end
go
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson"
--=_NextPart_000_04DA_01C3B8E9.27FD2C60--|||Sure, compare (a) the count(*) from inserted with (b) the count(*) from
deleted.
if (a) = 0 and (b) > 0, it's a delete
if (a) > 0 and (b) = 0, it's an insert
if (a) > 0 and (b) > 0, it's an update
(Not sure if (a)=0 and (b)=0 is possible, but this would mean that the
trigger was fired for nothing, e.g. 0 row(s) affected.)
See http://www.aspfaq.com/2496 for an example of control flow in a trigger,
based on the event.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:uK1T8IRuDHA.3436@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I was wondering if, within a trigger that's defined FOR INSERT, UPDATE,
can it determine
> whether the action that fired the trigger was an INSERT versus an UPDATE?
I've got a
> little "extra" logic to do for an UPDATE that I want to avoid with an
INSERT. Or do I
> need to have two separate triggers, and then potentially call out to a
third SP that has
> the "guts" of the current trigger?
> Thanks!
> John Peterson
>|||Ah, thanks guys -- I hadn't considered using those tables! I was trying to fiddle with
the COLUMNS_UPDATED() function, as BOL seemed to indicate that when an INSERT is invoked,
that function will return TRUE. However, I'm getting a syntax error when I try to use it
like that:
...
if ((not update(MySpecificCol)) or (columns_updated())) begin
...
end
...
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:uK1T8IRuDHA.3436@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I was wondering if, within a trigger that's defined FOR INSERT, UPDATE, can it determine
> whether the action that fired the trigger was an INSERT versus an UPDATE? I've got a
> little "extra" logic to do for an UPDATE that I want to avoid with an INSERT. Or do I
> need to have two separate triggers, and then potentially call out to a third SP that has
> the "guts" of the current trigger?
> Thanks!
> John Peterson
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0510_01C3B8EB.34932630
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
The columns_updated() function requires an argument.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eP1juRRuDHA.1876@.TK2MSFTNGP09.phx.gbl...
Ah, thanks guys -- I hadn't considered using those tables! I was trying to
fiddle with
the COLUMNS_UPDATED() function, as BOL seemed to indicate that when an
INSERT is invoked,
that function will return TRUE. However, I'm getting a syntax error when I
try to use it
like that:
...
if ((not update(MySpecificCol)) or (columns_updated())) begin
...
end
...
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:uK1T8IRuDHA.3436@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I was wondering if, within a trigger that's defined FOR INSERT, UPDATE,
can it determine
> whether the action that fired the trigger was an INSERT versus an UPDATE?
I've got a
> little "extra" logic to do for an UPDATE that I want to avoid with an
INSERT. Or do I
> need to have two separate triggers, and then potentially call out to a
third SP that has
> the "guts" of the current trigger?
> Thanks!
> John Peterson
>
--=_NextPart_000_0510_01C3B8EB.34932630
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
The columns_updated() function =requires an argument.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson"
--=_NextPart_000_0510_01C3B8EB.34932630--|||I meant to include the BOL snippet:
<Quote>
COLUMNS_UPDATED will return the TRUE value for all columns in INSERT actions because the
columns have either explicit values or implicit (NULL) values inserted.
</Quote>
But, as I say, I can't quite get that function to work in this context. Unless I should
just be reading this as all "bits" will be on (TRUE) in an INSERT context? I wonder if
there's a quick/easy way to determine that?
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eP1juRRuDHA.1876@.TK2MSFTNGP09.phx.gbl...
> Ah, thanks guys -- I hadn't considered using those tables! I was trying to fiddle with
> the COLUMNS_UPDATED() function, as BOL seemed to indicate that when an INSERT is
invoked,
> that function will return TRUE. However, I'm getting a syntax error when I try to use
it
> like that:
> ...
> if ((not update(MySpecificCol)) or (columns_updated())) begin
> ...
> end
> ...
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:uK1T8IRuDHA.3436@.tk2msftngp13.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > I was wondering if, within a trigger that's defined FOR INSERT, UPDATE, can it
determine
> > whether the action that fired the trigger was an INSERT versus an UPDATE? I've got a
> > little "extra" logic to do for an UPDATE that I want to avoid with an INSERT. Or do I
> > need to have two separate triggers, and then potentially call out to a third SP that
has
> > the "guts" of the current trigger?
> >
> > Thanks!
> >
> > John Peterson
> >
> >
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0537_01C3B8EC.A1EC5AC0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
What's wrong with the method I posted?
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:e8d2sWRuDHA.536@.tk2msftngp13.phx.gbl...
I meant to include the BOL snippet:
<Quote>
COLUMNS_UPDATED will return the TRUE value for all columns in INSERT actions
because the
columns have either explicit values or implicit (NULL) values inserted.
</Quote>
But, as I say, I can't quite get that function to work in this context.
Unless I should
just be reading this as all "bits" will be on (TRUE) in an INSERT context?
I wonder if
there's a quick/easy way to determine that?
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eP1juRRuDHA.1876@.TK2MSFTNGP09.phx.gbl...
> Ah, thanks guys -- I hadn't considered using those tables! I was trying
to fiddle with
> the COLUMNS_UPDATED() function, as BOL seemed to indicate that when an
INSERT is
invoked,
> that function will return TRUE. However, I'm getting a syntax error when
I try to use
it
> like that:
> ...
> if ((not update(MySpecificCol)) or (columns_updated())) begin
> ...
> end
> ...
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:uK1T8IRuDHA.3436@.tk2msftngp13.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > I was wondering if, within a trigger that's defined FOR INSERT, UPDATE,
can it
determine
> > whether the action that fired the trigger was an INSERT versus an
UPDATE? I've got a
> > little "extra" logic to do for an UPDATE that I want to avoid with an
INSERT. Or do I
> > need to have two separate triggers, and then potentially call out to a
third SP that
has
> > the "guts" of the current trigger?
> >
> > Thanks!
> >
> > John Peterson
> >
> >
>
--=_NextPart_000_0537_01C3B8EC.A1EC5AC0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
What's wrong with the method I posted?
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson"
--=_NextPart_000_0537_01C3B8EC.A1EC5AC0--|||This is a multi-part message in MIME format.
--=_NextPart_000_00D6_01C3B8DC.C739DD80
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Thanks Tom! There's nothing wrong with the method that you posted -- I =guess I was just using this as an exercise to learn more about the =COLUMNS_UPDATED() function, and whether using that might be more =performant. :-)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:uBwQaaRuDHA.2360@.TK2MSFTNGP10.phx.gbl...
What's wrong with the method I posted?
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:e8d2sWRuDHA.536@.tk2msftngp13.phx.gbl...
I meant to include the BOL snippet:
<Quote>
COLUMNS_UPDATED will return the TRUE value for all columns in INSERT =actions because the
columns have either explicit values or implicit (NULL) values =inserted.
</Quote>
But, as I say, I can't quite get that function to work in this =context. Unless I should
just be reading this as all "bits" will be on (TRUE) in an INSERT =context? I wonder if
there's a quick/easy way to determine that?
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eP1juRRuDHA.1876@.TK2MSFTNGP09.phx.gbl...
> Ah, thanks guys -- I hadn't considered using those tables! I was =trying to fiddle with
> the COLUMNS_UPDATED() function, as BOL seemed to indicate that when =an INSERT is
invoked,
> that function will return TRUE. However, I'm getting a syntax error =when I try to use
it
> like that:
>
> ...
> if ((not update(MySpecificCol)) or (columns_updated())) begin
> ...
> end
> ...
>
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:uK1T8IRuDHA.3436@.tk2msftngp13.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > I was wondering if, within a trigger that's defined FOR INSERT, =UPDATE, can it
determine
> > whether the action that fired the trigger was an INSERT versus an =UPDATE? I've got a
> > little "extra" logic to do for an UPDATE that I want to avoid with =an INSERT. Or do I
> > need to have two separate triggers, and then potentially call out =to a third SP that
has
> > the "guts" of the current trigger?
> >
> > Thanks!
> >
> > John Peterson
> >
> >
>
>
--=_NextPart_000_00D6_01C3B8DC.C739DD80
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Thanks Tom! There's nothing =wrong with the method that you posted -- I guess I was just using this as an exercise =to learn more about the COLUMNS_UPDATED() function, and whether using that might =be more performant. :-)
"Tom Moreau"
What's wrong with the method I posted?
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson"
--=_NextPart_000_00D6_01C3B8DC.C739DD80--|||This is a multi-part message in MIME format.
--=_NextPart_000_0579_01C3B8EE.E25FEB10
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
Well, I'm not a big fan of COLUMNS_UPDATED(). If you decide to change the
ordinal position of various columns, then your trigger code will have to
change. Often, developers forget to update the trigger when that happens.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:uONX2dRuDHA.2448@.TK2MSFTNGP12.phx.gbl...
Thanks Tom! There's nothing wrong with the method that you posted -- I
guess I was just using this as an exercise to learn more about the
COLUMNS_UPDATED() function, and whether using that might be more performant.
:-)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uBwQaaRuDHA.2360@.TK2MSFTNGP10.phx.gbl...
What's wrong with the method I posted?
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:e8d2sWRuDHA.536@.tk2msftngp13.phx.gbl...
I meant to include the BOL snippet:
<Quote>
COLUMNS_UPDATED will return the TRUE value for all columns in INSERT
actions because the
columns have either explicit values or implicit (NULL) values inserted.
</Quote>
But, as I say, I can't quite get that function to work in this context.
Unless I should
just be reading this as all "bits" will be on (TRUE) in an INSERT context?
I wonder if
there's a quick/easy way to determine that?
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eP1juRRuDHA.1876@.TK2MSFTNGP09.phx.gbl...
> Ah, thanks guys -- I hadn't considered using those tables! I was trying
to fiddle with
> the COLUMNS_UPDATED() function, as BOL seemed to indicate that when an
INSERT is
invoked,
> that function will return TRUE. However, I'm getting a syntax error
when I try to use
it
> like that:
>
> ...
> if ((not update(MySpecificCol)) or (columns_updated())) begin
> ...
> end
> ...
>
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:uK1T8IRuDHA.3436@.tk2msftngp13.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > I was wondering if, within a trigger that's defined FOR INSERT,
UPDATE, can it
determine
> > whether the action that fired the trigger was an INSERT versus an
UPDATE? I've got a
> > little "extra" logic to do for an UPDATE that I want to avoid with an
INSERT. Or do I
> > need to have two separate triggers, and then potentially call out to a
third SP that
has
> > the "guts" of the current trigger?
> >
> > Thanks!
> >
> > John Peterson
> >
> >
>
>
--=_NextPart_000_0579_01C3B8EE.E25FEB10
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Well, I'm not a big fan of COLUMNS_UPDATED(). If you decide to change the ordinal position of =various columns, then your trigger code will have to change. Often, =developers forget to update the trigger when that happens.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson"
Thanks Tom! There's nothing =wrong with the method that you posted -- I guess I was just using this as an exercise =to learn more about the COLUMNS_UPDATED() function, and whether using that might =be more performant. :-)
"Tom Moreau"
What's wrong with the method I posted?
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson"
--=_NextPart_000_0579_01C3B8EE.E25FEB10--|||This is a multi-part message in MIME format.
--=_NextPart_000_00F0_01C3B8E2.6B0B2CC0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Understood -- I had hoped there would be a quick and easy mechanism to =essentially create a bitmask that was representative of all the columns =in the table (without regard to the position of the columns). Then, =simply compare this value with the COLUMNS_UPDATED() value. But, I =think something like that would be far more onerous than your (and =Aaron's) recommendation. :-)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:ebVwVjRuDHA.2308@.TK2MSFTNGP09.phx.gbl...
Well, I'm not a big fan of COLUMNS_UPDATED(). If you decide to change =the ordinal position of various columns, then your trigger code will =have to change. Often, developers forget to update the trigger when =that happens.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:uONX2dRuDHA.2448@.TK2MSFTNGP12.phx.gbl...
Thanks Tom! There's nothing wrong with the method that you posted -- =I guess I was just using this as an exercise to learn more about the =COLUMNS_UPDATED() function, and whether using that might be more =performant. :-)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:uBwQaaRuDHA.2360@.TK2MSFTNGP10.phx.gbl...
What's wrong with the method I posted?
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:e8d2sWRuDHA.536@.tk2msftngp13.phx.gbl...
I meant to include the BOL snippet:
<Quote>
COLUMNS_UPDATED will return the TRUE value for all columns in INSERT =actions because the
columns have either explicit values or implicit (NULL) values =inserted.
</Quote>
But, as I say, I can't quite get that function to work in this =context. Unless I should
just be reading this as all "bits" will be on (TRUE) in an INSERT =context? I wonder if
there's a quick/easy way to determine that?
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eP1juRRuDHA.1876@.TK2MSFTNGP09.phx.gbl...
> Ah, thanks guys -- I hadn't considered using those tables! I was =trying to fiddle with
> the COLUMNS_UPDATED() function, as BOL seemed to indicate that =when an INSERT is
invoked,
> that function will return TRUE. However, I'm getting a syntax =error when I try to use
it
> like that:
>
> ...
> if ((not update(MySpecificCol)) or (columns_updated())) begin
> ...
> end
> ...
>
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:uK1T8IRuDHA.3436@.tk2msftngp13.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > I was wondering if, within a trigger that's defined FOR INSERT, =UPDATE, can it
determine
> > whether the action that fired the trigger was an INSERT versus =an UPDATE? I've got a
> > little "extra" logic to do for an UPDATE that I want to avoid =with an INSERT. Or do I
> > need to have two separate triggers, and then potentially call =out to a third SP that
has
> > the "guts" of the current trigger?
> >
> > Thanks!
> >
> > John Peterson
> >
> >
>
>
--=_NextPart_000_00F0_01C3B8E2.6B0B2CC0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Understood -- I had hoped there =would be a quick and easy mechanism to essentially create a bitmask that was =representative of all the columns in the table (without regard to the position of the columns). Then, simply compare this value with the =COLUMNS_UPDATED() value. But, I think something like that would be far more onerous =than your (and Aaron's) recommendation. :-)
"Tom Moreau"
Well, I'm not a big fan of COLUMNS_UPDATED(). If you decide to change the ordinal position =of various columns, then your trigger code will have to change. =Often, developers forget to update the trigger when that =happens.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson"
Thanks Tom! There's nothing =wrong with the method that you posted -- I guess I was just using this as an =exercise to learn more about the COLUMNS_UPDATED() function, and whether using =that might be more performant. :-)
"Tom Moreau"
What's wrong with the method I posted?
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson"
--=_NextPart_000_00F0_01C3B8E2.6B0B2CC0--
No comments:
Post a Comment