How to know if INSERTing or UPDATing?

Hi all.
I am quite new to SQLServer 2005 and I cannot seem to solve it.
If we have a INSERT, UPDATE trigger, how do we know if the action that
fired it is an INSERT or an UPDATE?
Looking at 'deleted' virtual values perhaps?
COLUMNS_UPDATED() yields > 0 even for INSERTS.
TIA
Cheers.
Carlos.Carlos,
Rows in inserted and deleted = UPDATE
Rows in inserted only = INSERT
Rows in deleted only = DELETE
BOL says: COLUMNS_UPDATED returns TRUE for all columns in INSERT actions
because the columns have either explicit values or implicit (NULL) values
inserted.
RLF
"CarlosAL" <miotromailcarlos@.netscape.net> wrote in message
news:1181228538.517937.317920@.o5g2000hsb.googlegroups.com...
> Hi all.
> I am quite new to SQLServer 2005 and I cannot seem to solve it.
> If we have a INSERT, UPDATE trigger, how do we know if the action that
> fired it is an INSERT or an UPDATE?
> Looking at 'deleted' virtual values perhaps?
> COLUMNS_UPDATED() yields > 0 even for INSERTS.
> TIA
> Cheers.
> Carlos.
>|||> Looking at 'deleted' virtual values perhaps?
Yes,
if exists (select 1 from deleted) and exists (select 1 from inserted)
' update|||On 7 jun, 17:16, "Russell Fields" <russellfie...@.nomail.com> wrote:
> Carlos,
> Rows in inserted and deleted = UPDATE
> Rows in inserted only = INSERT
> Rows in deleted only = DELETE
> BOL says: COLUMNS_UPDATED returns TRUE for all columns in INSERT actions
> because the columns have either explicit values or implicit (NULL) values
> inserted.
> RLF
> "CarlosAL" <miotromailcar...@.netscape.net> wrote in message
> news:1181228538.517937.317920@.o5g2000hsb.googlegroups.com...
> > Hi all.
> > I am quite new to SQLServer 2005 and I cannot seem to solve it.
> > If we have a INSERT, UPDATE trigger, how do we know if the action that
> > fired it is an INSERT or an UPDATE?
> > Looking at 'deleted' virtual values perhaps?
> > COLUMNS_UPDATED() yields > 0 even for INSERTS.
> > TIA
> > Cheers.
> > Carlos.
Yep!
I was suspecting someting like that...
Thank you so much Russell.
Its a long long way to go from Oracle to SQL Server...
Cheers.
Carlos.|||On 7 jun, 17:23, CarlosAL <miotromailcar...@.netscape.net> wrote:
> On 7 jun, 17:16, "Russell Fields" <russellfie...@.nomail.com> wrote:
>
> > Carlos,
> > Rows in inserted and deleted = UPDATE
> > Rows in inserted only = INSERT
> > Rows in deleted only = DELETE
> > BOL says: COLUMNS_UPDATED returns TRUE for all columns in INSERT actions
> > because the columns have either explicit values or implicit (NULL) values
> > inserted.
> > RLF
> > "CarlosAL" <miotromailcar...@.netscape.net> wrote in message
> >news:1181228538.517937.317920@.o5g2000hsb.googlegroups.com...
> > > Hi all.
> > > I am quite new to SQLServer 2005 and I cannot seem to solve it.
> > > If we have a INSERT, UPDATE trigger, how do we know if the action that
> > > fired it is an INSERT or an UPDATE?
> > > Looking at 'deleted' virtual values perhaps?
> > > COLUMNS_UPDATED() yields > 0 even for INSERTS.
> > > TIA
> > > Cheers.
> > > Carlos.
> Yep!
> I was suspecting someting like that...
> Thank you so much Russell.
> Its a long long way to go from Oracle to SQL Server...
> Cheers.
> Carlos.
...and thank you Aaron too! (You posted while I was writing)
Cheers.
Carlos|||On Jun 7, 8:02 pm, CarlosAL <miotromailcar...@.netscape.net> wrote:
> Hi all.
> I am quite new to SQLServer 2005 and I cannot seem to solve it.
> If we have a INSERT, UPDATE trigger, how do we know if the action that
> fired it is an INSERT or an UPDATE?
> Looking at 'deleted' virtual values perhaps?
> COLUMNS_UPDATED() yields > 0 even for INSERTS.
> TIA
> Cheers.
> Carlos.
I think checking rows in inserted and deleted is a crude way . If
Trigger does not insert or update rows in the table due to some
where condition, these system tables will not have any rows. Some
times it may be required to capture the event .
MS should provide a function to capture which one of the INSERT/UPDATE/
DELETE events prompted trigger to execute|||> I think checking rows in inserted and deleted is a crude way . If
> Trigger does not insert or update rows in the table due to some
> where condition, these system tables will not have any rows.
And so any IF EXISTS check will return false. Anyway, your trigger should
have IF @.@.ROWCOUNT > 0 before executing anything anyway. That way if no
rows are affected, the trigger exits cleanly.|||On Thu, 07 Jun 2007 08:02:18 -0700, CarlosAL wrote:
>Hi all.
>I am quite new to SQLServer 2005 and I cannot seem to solve it.
>If we have a INSERT, UPDATE trigger, how do we know if the action that
>fired it is an INSERT or an UPDATE?
Hi Carlos,
If the trigger has to do different things on insert and update, why not
create two triggers? Saves you the need to find out what the action was,
and might result in better performance to boot!
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On 9 jun, 23:33, Hugo Kornelis <h...@.perFact.REMOVETHIS.info.INVALID>
wrote:
> On Thu, 07 Jun 2007 08:02:18 -0700, CarlosAL wrote:
> >Hi all.
> >I am quite new to SQLServer 2005 and I cannot seem to solve it.
> >If we have a INSERT, UPDATE trigger, how do we know if the action that
> >fired it is an INSERT or an UPDATE?
> Hi Carlos,
> If the trigger has to do different things on insert and update, why not
> create two triggers? Saves you the need to find out what the action was,
> and might result in better performance to boot!
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
Thanks to all.
Hugo: The problem here is the developer (not me) decided to implement
TWO triggers in order to provide values for audit columns. If we have
two triggers (basically they do the same thing), when we UPDATE the
(auditory) values in an insert, we fire the second trigger, which is
not desiseable: two triggers to do one (same) thing.
Cheers and thanks again.
Carlos.|||You can easily add logic to your update stored procedure that ignores
updates that affect the auditing columns. Have a look at IF
UPDATE(column_name) ...
"CarlosAL" <miotromailcarlos@.netscape.net> wrote in message
news:1181544855.466045.265580@.q75g2000hsh.googlegroups.com...
> On 9 jun, 23:33, Hugo Kornelis <h...@.perFact.REMOVETHIS.info.INVALID>
> wrote:
>> On Thu, 07 Jun 2007 08:02:18 -0700, CarlosAL wrote:
>> >Hi all.
>> >I am quite new to SQLServer 2005 and I cannot seem to solve it.
>> >If we have a INSERT, UPDATE trigger, how do we know if the action that
>> >fired it is an INSERT or an UPDATE?
>> Hi Carlos,
>> If the trigger has to do different things on insert and update, why not
>> create two triggers? Saves you the need to find out what the action was,
>> and might result in better performance to boot!
>> --
>> Hugo Kornelis, SQL Server MVP
>> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
> Thanks to all.
> Hugo: The problem here is the developer (not me) decided to implement
> TWO triggers in order to provide values for audit columns. If we have
> two triggers (basically they do the same thing), when we UPDATE the
> (auditory) values in an insert, we fire the second trigger, which is
> not desiseable: two triggers to do one (same) thing.
> Cheers and thanks again.
> Carlos.
>