Trigger Question in Sql 2005

5 Jan 2006 5:29 PM

Bob

I have two triggers that execute on update, Trigger 1 also executes on
insert , I need it to check to see if an update is allowed
ALTER trigger [trg_Mytable_ins_upd] on [dbo].[MyTable]

for insert, update

as

set nocount on;

Declare @IsClosed bit

IF UPDATE(Isclosed)

BEGIN

Set @IsClosed = (Select Isclosed From Inserted)

IF @ISclosed = 1

BEGIN

-- next is pseudo code

-- if the new value of Isclosed = true then do some checking to see if that
can be allowed to proceed

    --IF NOT OK

    --BEGIN

        --RAISERROR('Closing not allowed!', 16, 1)

        --ROLLBACK TRAN

-- HERE's The question how do I exit the trigger after the rollback without
going thru other T_SQL statements that may be after

-- the last end below and if I rollback on Trigger 1 on Update like this
will Trigger 2 that normally executes on update be stopped or

-- will it execute regardless?

    --END

END

5 Jan 2006 10:03 PM

Louis Davidson

First, triggers aren't row based, they are set based, so this code will fail
you if > 1 rows is updated.

Set @IsClosed = (Select Isclosed From Inserted)

You will need to formulate your queries in a row-based style to make this
work. If you post more information about what you are doing, one of us can
help you out with the query. Alternatively, as your first statement put
something like:

IF @@rowcount = 0
BEGIN
      RAISERROR('You many not insert or update more than one row at a time',
16, 1)
      ROLLBACK TRAN
      RETURN
END

Which answers the original question, use RETURN :)

--
----------------------------------------------------------------------------
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)

Show quote

"Bob" <bduf***@sgiims.com> wrote in message
news:ujhvX2hEGHA.3920@tk2msftngp13.phx.gbl...
>I have two triggers that execute on update, Trigger 1 also executes on
>insert , I need it to check to see if an update is allowed
> ALTER trigger [trg_Mytable_ins_upd] on [dbo].[MyTable]
>
> for insert, update
>
> as
>
> set nocount on;
>
> Declare @IsClosed bit
>
>
>
> IF UPDATE(Isclosed)
>
> BEGIN
>
> Set @IsClosed = (Select Isclosed From Inserted)
>
> IF @ISclosed = 1
>
> BEGIN
>
> -- next is pseudo code
>
> -- if the new value of Isclosed = true then do some checking to see if
> that can be allowed to proceed
>
>    --IF NOT OK
>
>    --BEGIN
>
>        --RAISERROR('Closing not allowed!', 16, 1)
>
>        --ROLLBACK TRAN
>
> -- HERE's The question how do I exit the trigger after the rollback
> without going thru other T_SQL statements that may be after
>
> -- the last end below and if I rollback on Trigger 1 on Update like this
> will Trigger 2 that normally executes on update be stopped or
>
> -- will it execute regardless?
>
>
>
>    --END
>
>
>
> END
>
>
>

5 Jan 2006 10:10 PM

Hugo Kornelis

On Thu, 5 Jan 2006 12:29:31 -0500, Bob wrote:

>I have two triggers that execute on update, Trigger 1 also executes on
>insert , I need it to check to see if an update is allowed

(snip)

>    --IF NOT OK
>
>    --BEGIN
>
>        --RAISERROR('Closing not allowed!', 16, 1)
>
>        --ROLLBACK TRAN
>
>-- HERE's The question how do I exit the trigger after the rollback without
>going thru other T_SQL statements that may be after
>
>-- the last end below

Hi Bob,

Use control-of-flow statements: either have the rest of the trigger in
an ELSE clause (see (1) below), or use a GOTO (see (2) below).

(1) Use ELSE clause

IF NOT OK
BEGIN
   RAISSERROR
   ROLLBACK TRAN
END
ELSE
BEGIN
   -- Rest of trigger code here
END
go

(2) Use GOTO

IF NOT OK
BEGIN
   RAISSERROR
   ROLLBACK TRAN
   GOTO EndLabel
END
-- Rest of trigger code here
EndLabel:
go

> and if I rollback on Trigger 1 on Update like this
>will Trigger 2 that normally executes on update be stopped or
>
>-- will it execute regardless?

On SQL Server 2000, no other triggers are executed after a trigger
initiates a rollback. You can use the script below to test if this
applies to SQL Server 2005 as well (I can't test it, as I don't have SQL
Server 2005 yet).

create table test (a int)
go
create trigger one on test after insert
as
print 'One -- before rollback'
rollback transaction
print 'One -- after rollback'
go
create trigger two on test after insert
as
print 'Two -- never displayed because of rollback in One'
rollback transaction
go
insert into test values(1)
go
drop table test
go

--
Hugo Kornelis, SQL Server MVP

5 Jan 2006 10:28 PM

Bob

Thanks to both of you.

Bob
Show quote

"Hugo Kornelis" <h***@perFact.REMOVETHIS.info> wrote in message
news:uv5rr1p7ej1hjo83qudn0p2ghkig9pk4cb@4ax.com...
> On Thu, 5 Jan 2006 12:29:31 -0500, Bob wrote:
>
>>I have two triggers that execute on update, Trigger 1 also executes on
>>insert , I need it to check to see if an update is allowed
>
> (snip)
>
>>    --IF NOT OK
>>
>>    --BEGIN
>>
>>        --RAISERROR('Closing not allowed!', 16, 1)
>>
>>        --ROLLBACK TRAN
>>
>>-- HERE's The question how do I exit the trigger after the rollback
>>without
>>going thru other T_SQL statements that may be after
>>
>>-- the last end below
>
> Hi Bob,
>
> Use control-of-flow statements: either have the rest of the trigger in
> an ELSE clause (see (1) below), or use a GOTO (see (2) below).
>
> (1) Use ELSE clause
>
> IF NOT OK
> BEGIN
>   RAISSERROR
>   ROLLBACK TRAN
> END
> ELSE
> BEGIN
>   -- Rest of trigger code here
> END
> go
>
> (2) Use GOTO
>
> IF NOT OK
> BEGIN
>   RAISSERROR
>   ROLLBACK TRAN
>   GOTO EndLabel
> END
> -- Rest of trigger code here
> EndLabel:
> go
>
>> and if I rollback on Trigger 1 on Update like this
>>will Trigger 2 that normally executes on update be stopped or
>>
>>-- will it execute regardless?
>
> On SQL Server 2000, no other triggers are executed after a trigger
> initiates a rollback. You can use the script below to test if this
> applies to SQL Server 2005 as well (I can't test it, as I don't have SQL
> Server 2005 yet).
>
> create table test (a int)
> go
> create trigger one on test after insert
> as
> print 'One -- before rollback'
> rollback transaction
> print 'One -- after rollback'
> go
> create trigger two on test after insert
> as
> print 'Two -- never displayed because of rollback in One'
> rollback transaction
> go
> insert into test values(1)
> go
> drop table test
> go
>
>
> --
> Hugo Kornelis, SQL Server MVP

5 Jan 2006 10:42 PM

Bob

> On SQL Server 2000, no other triggers are executed after a trigger
> initiates a rollback. You can use the script below to test if this
> applies to SQL Server 2005 as well (I can't test it, as I don't have SQL
> Server 2005 yet).

I think SQl 2005 behaves the same way as 2000 in this regard

I noticed that when I tested the trigger and it rolled back, my
lastupdatedBy and last updatedOn fields did not get modified in the table.

Bob

Show quote

"Hugo Kornelis" <h***@perFact.REMOVETHIS.info> wrote in message
news:uv5rr1p7ej1hjo83qudn0p2ghkig9pk4cb@4ax.com...
> On Thu, 5 Jan 2006 12:29:31 -0500, Bob wrote:
>
>>I have two triggers that execute on update, Trigger 1 also executes on
>>insert , I need it to check to see if an update is allowed
>
> (snip)
>
>>    --IF NOT OK
>>
>>    --BEGIN
>>
>>        --RAISERROR('Closing not allowed!', 16, 1)
>>
>>        --ROLLBACK TRAN
>>
>>-- HERE's The question how do I exit the trigger after the rollback
>>without
>>going thru other T_SQL statements that may be after
>>
>>-- the last end below
>
> Hi Bob,
>
> Use control-of-flow statements: either have the rest of the trigger in
> an ELSE clause (see (1) below), or use a GOTO (see (2) below).
>
> (1) Use ELSE clause
>
> IF NOT OK
> BEGIN
>   RAISSERROR
>   ROLLBACK TRAN
> END
> ELSE
> BEGIN
>   -- Rest of trigger code here
> END
> go
>
> (2) Use GOTO
>
> IF NOT OK
> BEGIN
>   RAISSERROR
>   ROLLBACK TRAN
>   GOTO EndLabel
> END
> -- Rest of trigger code here
> EndLabel:
> go
>
>> and if I rollback on Trigger 1 on Update like this
>>will Trigger 2 that normally executes on update be stopped or
>>
>>-- will it execute regardless?
>
> On SQL Server 2000, no other triggers are executed after a trigger
> initiates a rollback. You can use the script below to test if this
> applies to SQL Server 2005 as well (I can't test it, as I don't have SQL
> Server 2005 yet).
>
> create table test (a int)
> go
> create trigger one on test after insert
> as
> print 'One -- before rollback'
> rollback transaction
> print 'One -- after rollback'
> go
> create trigger two on test after insert
> as
> print 'Two -- never displayed because of rollback in One'
> rollback transaction
> go
> insert into test values(1)
> go
> drop table test
> go
>
>
> --
> Hugo Kornelis, SQL Server MVP