I want to be able to find the differences between the before and after
values in a table as updates occur. I thought an easy way to do this
would be to create another table with an identical structure and then
use an update trigger to insert the deleted and inserted rows into
that alternate table. I know what order the rows are in the table
since I will put them in there but how can I, without a time value
column, know which one was inserted into the table first?You can't. It would be very easy to add a column, e.g., InsertDate, with a
default of CURRENT_TIMESTAMP. In your alternate table you wouldn't need a
default on it.
HTH
Vern Rabe
"Computer User" wrote:
> I want to be able to find the differences between the before and after
> values in a table as updates occur. I thought an easy way to do this
> would be to create another table with an identical structure and then
> use an update trigger to insert the deleted and inserted rows into
> that alternate table. I know what order the rows are in the table
> since I will put them in there but how can I, without a time value
> column, know which one was inserted into the table first?
>|||ordering is an aspect of data selection, so you need some sort of
ordering column to indicate time based data.
there's no such thing intrinsically in a sql table as a row number, so
you really don't know what order the rows are in the table.
why wouldn't you want a datetime stamp column?
if you care that data was changed, wouldn't you want to know when it
changed?
you'll probably also want an indicator for which row it was
[deleted/inserted]
Computer User wrote:
> I want to be able to find the differences between the before and after
> values in a table as updates occur. I thought an easy way to do this
> would be to create another table with an identical structure and then
> use an update trigger to insert the deleted and inserted rows into
> that alternate table. I know what order the rows are in the table
> since I will put them in there but how can I, without a time value
> column, know which one was inserted into the table first?|||On Wed, 04 Jan 2006 17:35:05 -0600, Trey Walpole
<treypole@.newsgroups.nospam> wrote:
>ordering is an aspect of data selection, so you need some sort of
>ordering column to indicate time based data.
>there's no such thing intrinsically in a sql table as a row number, so
>you really don't know what order the rows are in the table.
>
I know that selection usually includes an "order by" clause, but the
data must be in the db in some order.
>why wouldn't you want a datetime stamp column?
>if you care that data was changed, wouldn't you want to know when it
>changed?
>
In this instance, I don't care when the data was changed, only that it
was. A web application is supposed to send an email to an
administrator showing db modifications. Having a "before" row and an
"after" row would make this easy.
>you'll probably also want an indicator for which row it was
>[deleted/inserted]
>
If I knew the order I would know which row it was because I will
insert the deleted row before the inserted row.|||email notifications aren't necessarily terribly reliable.
I find it advisable to have a screen ( as well ) where you can see
notifications.
If you want to be sure of the order then I suggest writing to a log
file would be better than a table.
The order that data is in will not be useful otherwise.
I would recommend creating a table which has a bunch of fields for
before and the same again for after.
Plus your primary (unique ) key, a datestamp and change indicator (
Insert, Update, Delete ).
Write this with your trigger.
What I'd do with it then depends on how dynamic the data is.
I would hope that it's not very dynamic of all this is almost certainly
a complete waste of time.
Anyhow.
Stick a screen on the front of your app that the administrator only
sees with the changes from yesterday and today presented on it.
Use the timestamp to drive the selection.|||Computer User wrote:
> On Wed, 04 Jan 2006 17:35:05 -0600, Trey Walpole
> <treypole@.newsgroups.nospam> wrote:
>
> I know that selection usually includes an "order by" clause, but the
> data must be in the db in some order.
It's in the database in some order, true. But there is no guarantee of
the order in which the server will retrieve rows, unless you impose an
ordering. It is *entirely* up to the server in what order it returns a
set of rows, and the order you receive them in may depend on server
version, patches, number of processors, *workload*, *data volumes*,
*indexes* and *statistics*. (the * ones are ones likely to change just
in the day-to-day use of a database). So if you need to retrieve data
in an order based on when it was inserted, you best record that
information.
In general, for small tables, your data will be returned to you in the
order determined by the clustered index (if it exists), or the order in
which data was inserted (if no clustered index). However, this is for
very small tables (I think as soon as you start using two pages, the
server can start reordering the rows as it sees fit, but not sure)
Damien|||Computer User wrote:
> On Wed, 04 Jan 2006 17:35:05 -0600, Trey Walpole
> <treypole@.newsgroups.nospam> wrote:
>
> I know that selection usually includes an "order by" clause, but the
> data must be in the db in some order.
>
no, it's not. it's wherever the dbms put it. it could be in order, it
might not be, even for clustered indexes.
there is no intrinsic row number or insertion order. if you want one,
you have to add one.
> In this instance, I don't care when the data was changed, only that it
> was. A web application is supposed to send an email to an
> administrator showing db modifications. Having a "before" row and an
> "after" row would make this easy.
>
so what's the problem with adding a column that will help you?
"i don't care when the data was changed..." - famous last words :)
> If I knew the order I would know which row it was because I will
> insert the deleted row before the inserted row.
if you really do not care and can honestly say that you will never care
when the data was changed, then you could add an identity column to your
auditing table.
your better bet would be a single row with before and after values for
each column being audited.
Showing posts with label determine. Show all posts
Showing posts with label determine. Show all posts
Thursday, March 22, 2012
Sunday, February 19, 2012
Can a trigger determine its name?
Hello,
is it possible for a trigger or stored procedure to determine its name?
thanks,
Vadim RappThis returns the name of the currently executing process:
SELECT OBJECT_NAME(@.@.PROCID)
David Portas
SQL Server MVP
--|||Vadim Rapp wrote:
> Hello,
> is it possible for a trigger or stored procedure to determine its
> name?
> thanks,
> Vadim Rapp
You can use something like:
create table abc1234 (col1 int)
go
create proc dbo.abc123
as
select object_name(@.@.PROCID)
go
create trigger abc1234_ins on dbo.abc1234
for insert
as
select object_name(@.@.PROCID)
exec abc123
insert into abc1234 values (5)
drop table abc1234
go
drop proc abc123
go
David Gugick
Imceda Software
www.imceda.com|||It is possible, using @.@.PROCID and OBJECT_NAME() :
CREATE PROCEDURE testprocedure AS
SELECT OBJECT_NAME(@.@.PROCID)
GO
EXEC testprocedure
GO
CREATE TABLE t (i int)
GO
CREATE TRIGGER testtrigger ON t AFTER INSERT
AS
DECLARE @.n SYSNAME
SET @.n = OBJECT_NAME(@.@.PROCID)
RAISERROR (@.n,16,1)
GO
INSERT INTO t(i) VALUES(1)
GO
DROP PROCEDURE testprocedure
GO
DROP TABLE t
Jacco Schalkwijk
SQL Server MVP
"Vadim Rapp" <vrapp@.nospam.polyscience.com> wrote in message
news:eBwN1UQRFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Hello,
> is it possible for a trigger or stored procedure to determine its name?
> thanks,
> Vadim Rapp|||Thanks everyone!
Vadim
is it possible for a trigger or stored procedure to determine its name?
thanks,
Vadim RappThis returns the name of the currently executing process:
SELECT OBJECT_NAME(@.@.PROCID)
David Portas
SQL Server MVP
--|||Vadim Rapp wrote:
> Hello,
> is it possible for a trigger or stored procedure to determine its
> name?
> thanks,
> Vadim Rapp
You can use something like:
create table abc1234 (col1 int)
go
create proc dbo.abc123
as
select object_name(@.@.PROCID)
go
create trigger abc1234_ins on dbo.abc1234
for insert
as
select object_name(@.@.PROCID)
exec abc123
insert into abc1234 values (5)
drop table abc1234
go
drop proc abc123
go
David Gugick
Imceda Software
www.imceda.com|||It is possible, using @.@.PROCID and OBJECT_NAME() :
CREATE PROCEDURE testprocedure AS
SELECT OBJECT_NAME(@.@.PROCID)
GO
EXEC testprocedure
GO
CREATE TABLE t (i int)
GO
CREATE TRIGGER testtrigger ON t AFTER INSERT
AS
DECLARE @.n SYSNAME
SET @.n = OBJECT_NAME(@.@.PROCID)
RAISERROR (@.n,16,1)
GO
INSERT INTO t(i) VALUES(1)
GO
DROP PROCEDURE testprocedure
GO
DROP TABLE t
Jacco Schalkwijk
SQL Server MVP
"Vadim Rapp" <vrapp@.nospam.polyscience.com> wrote in message
news:eBwN1UQRFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Hello,
> is it possible for a trigger or stored procedure to determine its name?
> thanks,
> Vadim Rapp|||Thanks everyone!
Vadim
Can a trigger determine INSERT/UPDATE context?
(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 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" 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 determinewhether the =action that fired the trigger was an INSERT versus an UPDATE? I've got =alittle "extra" logic to do for an UPDATE that I want to avoid with an =INSERT. Or do Ineed to have two separate triggers, and then potentially call =out to a third SP that hasthe "guts" of the current trigger?Thanks!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" wrote in =message news:eP1juRRuDHA.1876=@.TK2MSFTNGP09.phx.gbl...Ah, thanks guys -- I hadn't considered using those tables! I was =trying to fiddle withthe 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 itlike =that:...if ((not update(MySpecificCol)) or (columns_updated())) begin ...end..."John Peterson" wrote in =messagenews: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--|||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" wrote in =message news:e8d2sWRuDHA.536@.t=k2msftngp13.phx.gbl...I meant to include the BOL =snippet:COLUMNS_UPDATED will return the TRUE value for all columns in INSERT actions because =thecolumns have either explicit values or implicit (NULL) values inserted.But, as I say, I can't quite get that =function to work in this context. Unless I shouldjust be =reading this as all "bits" will be on (TRUE) in an INSERT context? I wonder ifthere's a quick/easy way to determine that?"John =Peterson" wrote =in messagenews: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 isinvoked,> that function will return =TRUE. However, I'm getting a syntax error when I try to useit> like =that:>> ...> if ((not update(MySpecificCol)) or (columns_updated())) begin> ...> =end> ...>>> "John Peterson" 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 itdetermine> > 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 thathas> > the "guts" of the current trigger?> >> > Thanks!> >> > =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"= wrote in message news:uBwQaaRuDHA.2360=@.TK2MSFTNGP10.phx.gbl...
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" wrote in =message news:e8d2sWRuDHA.536@.t=k2msftngp13.phx.gbl...I meant to include the BOL =snippet:COLUMNS_UPDATED will return the TRUE value for all columns in INSERT actions because =thecolumns have either explicit values or implicit (NULL) values inserted.But, as I say, I can't quite get =that function to work in this context. Unless I shouldjust be =reading this as all "bits" will be on (TRUE) in an INSERT context? I =wonder ifthere's a quick/easy way to determine that?"John =Peterson" =wrote in messagenews: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 isinvoked,> that function will =return TRUE. However, I'm getting a syntax error when I try to useit> like that:>> ...> if ((not update(MySpecificCol)) or (columns_updated())) =begin> ...> end> ...>>> "John Peterson" = 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 itdetermine> > 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 thathas> > the ="guts" of the current trigger?> >> > Thanks!> =>> > 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" 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"= wrote in message news:uBwQaaRuDHA.2360=@.TK2MSFTNGP10.phx.gbl...
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" wrote in =message news:e8d2sWRuDHA.536@.t=k2msftngp13.phx.gbl...I meant to include the BOL =snippet:COLUMNS_UPDATED will return the TRUE value for all columns in INSERT actions because =thecolumns have either explicit values or implicit (NULL) values inserted.But, as I say, I can't quite get =that function to work in this context. Unless I shouldjust be =reading this as all "bits" will be on (TRUE) in an INSERT context? I =wonder ifthere's a quick/easy way to determine that?"John =Peterson" =wrote in messagenews: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 isinvoked,> that function will =return TRUE. However, I'm getting a syntax error when I try to useit> like that:>> ...> if ((not update(MySpecificCol)) or (columns_updated())) =begin> ...> end> ...>>> "John Peterson" = 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 itdetermine> > 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 thathas> > the ="guts" of the current trigger?> >> > Thanks!> =>> > 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"= 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
=---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" 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"= wrote in message news:uBwQaaRuDHA.2360=@.TK2MSFTNGP10.phx.gbl...
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" wrote in =message news:e8d2sWRuDHA.536@.t=k2msftngp13.phx.gbl...I meant to include the BOL =snippet:COLUMNS_UPDATED will return the TRUE value for all columns in INSERT actions because = thecolumns have either explicit values or implicit (NULL) values = inserted.But, as I say, I can't quite get =that function to work in this context. Unless I shouldjust be =reading this as all "bits" will be on (TRUE) in an INSERT context? I =wonder ifthere's a quick/easy way to determine that?"John =Peterson" =wrote in messagenews: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 isinvoked,> that function =will return TRUE. However, I'm getting a syntax error when I try to = useit> like that:>> ...> if ((not update(MySpecificCol)) or (columns_updated())) begin> ...> end> ...>>> "John Peterson" 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 itdetermine> > 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 thathas> > the ="guts" of the current trigger?> >> > Thanks!> >> > John Peterson> >> >>>
--=_NextPart_000_00F0_01C3B8E2.6B0B2CC0--
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--
Subscribe to:
Posts (Atom)