[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Another trigger issue

Andre

3/23/2007 3:23:00 AM

I have 2 triggers which are listed below.

CREATE TRIGGER tester ON [dbo].[ENRLLMNT]
FOR INSERT, UPDATE
AS
update enrllmnt
set cntusrdateprocess = b.editdate
from inserted a inner join enrllmnt b on a.dwdocid = b.dwdocid
where convert(char(10), b.cntusrdateprocess,101) > convert(char(10),
b.editdate,101)

CREATE TRIGGER tester2 ON ENRLLMNT
After INSERT, UPDATE
AS
INSERT INTO ENRLLMNT_ARCHIVE
select *
from inserted

My issue is I need the trigger called tester to execute first since it is
changing records if the condition is met. I want the updated record to be
inserted into my archive table after that which is the trigger called tester2.
10 Answers

xyb

3/23/2007 5:20:00 AM

0

On 3?23?, ??11?23?, Andre <A...@discussions.microsoft.com> wrote:
> I have 2 triggers which are listed below.
>
> CREATE TRIGGER tester ON [dbo].[ENRLLMNT]
> FOR INSERT, UPDATE
> AS
> update enrllmnt
> set cntusrdateprocess = b.editdate
> from inserted a inner join enrllmnt b on a.dwdocid = b.dwdocid
> where convert(char(10), b.cntusrdateprocess,101) > convert(char(10),
> b.editdate,101)
>
> CREATE TRIGGER tester2 ON ENRLLMNT
> After INSERT, UPDATE
> AS
> INSERT INTO ENRLLMNT_ARCHIVE
> select *
> from inserted
>
> My issue is I need the trigger called tester to execute first since it is
> changing records if the condition is met. I want the updated record to be
> inserted into my archive table after that which is the trigger called tester2.

AFTER is default of FOR clause,although a table can have multi
triggers.
I have tested that no matter at what sequence your trigger created or
altered,
all the two triggers will excute after the record inserting or
updateing.
I am afraid your first trigger is a recursive trigger that you need to
think about.

Andre

3/23/2007 5:57:00 AM

0

So how do I update a column without a recursive trigger? And you really
didnt answer my original question of how to insert the updated record
afterwards.

"xyb" wrote:

> On 3æ??23æ?¥, ä¸?å?11æ?¶23å??, Andre <A...@discussions.microsoft.com> wrote:
> > I have 2 triggers which are listed below.
> >
> > CREATE TRIGGER tester ON [dbo].[ENRLLMNT]
> > FOR INSERT, UPDATE
> > AS
> > update enrllmnt
> > set cntusrdateprocess = b.editdate
> > from inserted a inner join enrllmnt b on a.dwdocid = b.dwdocid
> > where convert(char(10), b.cntusrdateprocess,101) > convert(char(10),
> > b.editdate,101)
> >
> > CREATE TRIGGER tester2 ON ENRLLMNT
> > After INSERT, UPDATE
> > AS
> > INSERT INTO ENRLLMNT_ARCHIVE
> > select *
> > from inserted
> >
> > My issue is I need the trigger called tester to execute first since it is
> > changing records if the condition is met. I want the updated record to be
> > inserted into my archive table after that which is the trigger called tester2.
>
> AFTER is default of FOR clause,although a table can have multi
> triggers.
> I have tested that no matter at what sequence your trigger created or
> altered,
> all the two triggers will excute after the record inserting or
> updateing.
> I am afraid your first trigger is a recursive trigger that you need to
> think about.
>
>

Tom Cooper

3/23/2007 6:05:00 AM

0

I have good news and bad news.

The good news is that if you have more than one trigger on a table for a
particular action (insert, update, delete), you can specify the one that
should go first and/or you can specify the one that should go last. See the
sp_settriggerorder documentation in BOL for how to do this.

The bad news is that if you have more than one trigger on a table for a
particular action and one or more of those triggers updates the table the
trigger is for, the processing that SQL Server will do may not be what you
expect. If you created your two triggers and set tester to fire first and
had the default value for the database option for recursive triggers (OFF)
and the default server option for nested triggers (ON), then SQL Server
would do the following when you inserted or updated rows in the ENRLLMNT
table.

First the insert or update would be done.

Second the tester trigger would fire. In the inserted table would be the
updated or inserted rows. If it was an update operation, the deleted table
would have the before images of the rows. The tester trigger updates the
ENRLLMNT table. Because the recursive triggers option is OFF, tester is not
fired again. But since the nested triggers option is ON, the tester2 trigger
will be fired because of this update in the tester trigger.

Third, the tester2 trigger runs because of the update done by tester. It
will have the rows as updated by tester in the inserted table and will
insert a copy of them in the ENRLLMNT_ARCHIVE table (which I think is what
you want done, BUT ...), now the tester2 trigger exits and to back to the
tester trigger and the tester trigger exits.

Fourth, now SQL Server fires the tester2 trigger again. Remember, the first
time tester2 ran was for the update done by tester, SQL Server still has to
fire the tester2 trigger for the original update. So tester2 will run
again, and this time the rows in the inserted table will have the values
from the original insert or update and tester2 will now insert a copy of
those rows into the ENRLLMNT_ARCHIVE table.

You can change what SQL Server does with these triggers by changing the
database option RECURSIVE_TRIGGERS and/or the server nested triggers
options. But changing the default values for these can have side effects
since the first affects all triggers in your database and the second affects
all triggers in your server (or all triggers in this instance of SQL Server
if you have multiple instances of SQL Server on your server).

If possible, I would recommend that you write just one trigger for each
operation (that is, it is fine, for example, to have one trigger for deletes
and one trigger for insert,update, but I would avoid having multiple
triggers for the same table for, for example, update). If you have only one
trigger, it is easy to control the order of operations. Multiple triggers
can do things that you don't expect.

Tom

"Andre" <Andre@discussions.microsoft.com> wrote in message
news:9F558BB3-EA64-459F-98E8-941F030FAEAF@microsoft.com...
>I have 2 triggers which are listed below.
>
> CREATE TRIGGER tester ON [dbo].[ENRLLMNT]
> FOR INSERT, UPDATE
> AS
> update enrllmnt
> set cntusrdateprocess = b.editdate
> from inserted a inner join enrllmnt b on a.dwdocid = b.dwdocid
> where convert(char(10), b.cntusrdateprocess,101) > convert(char(10),
> b.editdate,101)
>
> CREATE TRIGGER tester2 ON ENRLLMNT
> After INSERT, UPDATE
> AS
> INSERT INTO ENRLLMNT_ARCHIVE
> select *
> from inserted
>
> My issue is I need the trigger called tester to execute first since it is
> changing records if the condition is met. I want the updated record to be
> inserted into my archive table after that which is the trigger called
> tester2.


bob

3/23/2007 11:29:00 AM

0

Why not include the logic from your second trigger in the first:

CREATE TRIGGER tester ON [dbo].[ENRLLMNT]
FOR INSERT, UPDATE
AS

DECLARE @commit INT

SET @commit = 0

BEGIN TRAN

INSERT INTO ENRLLMNT_ARCHIVE ( dwdocid, cntusrdateprocess )
select a.dwdocid, b.editdate -- add any other columns you need
from inserted a
inner join enrllmnt b on a.dwdocid = b.dwdocid
where convert(char(10), b.cntusrdateprocess,101) > convert(char(10),
b.editdate,101)

update enrllmnt
set cntusrdateprocess = b.editdate
from inserted a
inner join enrllmnt b on a.dwdocid = b.dwdocid
where convert(char(10), b.cntusrdateprocess,101) > convert(char(10),
b.editdate,101)


SET @commit = 1

trigger_exit:

IF @@ERROR = 0 AND @commit = 1
COMMIT TRAN
ELSE
ROLLBACK TRAN

I haven't been able to test this, but hopefully you get the idea; mwrap in a
transaction to make sure both events happen, my trigger should have the same
net effect. Alternately, consider moving your logic to a stored procedure.

Let me know how you get on.

wBob


"Andre" wrote:

> I have 2 triggers which are listed below.
>
> CREATE TRIGGER tester ON [dbo].[ENRLLMNT]
> FOR INSERT, UPDATE
> AS
> update enrllmnt
> set cntusrdateprocess = b.editdate
> from inserted a inner join enrllmnt b on a.dwdocid = b.dwdocid
> where convert(char(10), b.cntusrdateprocess,101) > convert(char(10),
> b.editdate,101)
>
> CREATE TRIGGER tester2 ON ENRLLMNT
> After INSERT, UPDATE
> AS
> INSERT INTO ENRLLMNT_ARCHIVE
> select *
> from inserted
>
> My issue is I need the trigger called tester to execute first since it is
> changing records if the condition is met. I want the updated record to be
> inserted into my archive table after that which is the trigger called tester2.

Andre

3/23/2007 5:58:00 PM

0

Thanks for that wonderful response. Can you help me write something that will
work for my situation? I am trying to basically insert into my archive table
the result of the changed record from enrllmnt if the trigger criteria is
met, but the tester2 trigger that does that keeps inserting the original
record and not the changed record. I am not that experienced in triggers and
could use your help.

"Tom Cooper" wrote:

> I have good news and bad news.
>
> The good news is that if you have more than one trigger on a table for a
> particular action (insert, update, delete), you can specify the one that
> should go first and/or you can specify the one that should go last. See the
> sp_settriggerorder documentation in BOL for how to do this.
>
> The bad news is that if you have more than one trigger on a table for a
> particular action and one or more of those triggers updates the table the
> trigger is for, the processing that SQL Server will do may not be what you
> expect. If you created your two triggers and set tester to fire first and
> had the default value for the database option for recursive triggers (OFF)
> and the default server option for nested triggers (ON), then SQL Server
> would do the following when you inserted or updated rows in the ENRLLMNT
> table.
>
> First the insert or update would be done.
>
> Second the tester trigger would fire. In the inserted table would be the
> updated or inserted rows. If it was an update operation, the deleted table
> would have the before images of the rows. The tester trigger updates the
> ENRLLMNT table. Because the recursive triggers option is OFF, tester is not
> fired again. But since the nested triggers option is ON, the tester2 trigger
> will be fired because of this update in the tester trigger.
>
> Third, the tester2 trigger runs because of the update done by tester. It
> will have the rows as updated by tester in the inserted table and will
> insert a copy of them in the ENRLLMNT_ARCHIVE table (which I think is what
> you want done, BUT ...), now the tester2 trigger exits and to back to the
> tester trigger and the tester trigger exits.
>
> Fourth, now SQL Server fires the tester2 trigger again. Remember, the first
> time tester2 ran was for the update done by tester, SQL Server still has to
> fire the tester2 trigger for the original update. So tester2 will run
> again, and this time the rows in the inserted table will have the values
> from the original insert or update and tester2 will now insert a copy of
> those rows into the ENRLLMNT_ARCHIVE table.
>
> You can change what SQL Server does with these triggers by changing the
> database option RECURSIVE_TRIGGERS and/or the server nested triggers
> options. But changing the default values for these can have side effects
> since the first affects all triggers in your database and the second affects
> all triggers in your server (or all triggers in this instance of SQL Server
> if you have multiple instances of SQL Server on your server).
>
> If possible, I would recommend that you write just one trigger for each
> operation (that is, it is fine, for example, to have one trigger for deletes
> and one trigger for insert,update, but I would avoid having multiple
> triggers for the same table for, for example, update). If you have only one
> trigger, it is easy to control the order of operations. Multiple triggers
> can do things that you don't expect.
>
> Tom
>
> "Andre" <Andre@discussions.microsoft.com> wrote in message
> news:9F558BB3-EA64-459F-98E8-941F030FAEAF@microsoft.com...
> >I have 2 triggers which are listed below.
> >
> > CREATE TRIGGER tester ON [dbo].[ENRLLMNT]
> > FOR INSERT, UPDATE
> > AS
> > update enrllmnt
> > set cntusrdateprocess = b.editdate
> > from inserted a inner join enrllmnt b on a.dwdocid = b.dwdocid
> > where convert(char(10), b.cntusrdateprocess,101) > convert(char(10),
> > b.editdate,101)
> >
> > CREATE TRIGGER tester2 ON ENRLLMNT
> > After INSERT, UPDATE
> > AS
> > INSERT INTO ENRLLMNT_ARCHIVE
> > select *
> > from inserted
> >
> > My issue is I need the trigger called tester to execute first since it is
> > changing records if the condition is met. I want the updated record to be
> > inserted into my archive table after that which is the trigger called
> > tester2.
>
>
>

Tom Cooper

3/23/2007 6:32:00 PM

0

I would do something prety close to what Bob showed you. I would change a
few things, first there is no need to do a BEGIN TRAN in a trigger, whenever
a trigger is running, it is always in a transaction (even if the application
didn't do a transaction, the insert/update/delete that fires a trigger and
the execution of the fired trigger(s) are always in an implicit transaction.
Second, I don't think you want to use type 101 in your calls to Convert() in
the where clause. Type 101 converts the datetime to a character string of
the format 'mm/dd/yyyy'. So Jan 1, 2007 would be '01/01/2007' and Feb 15,
1953 would be '02/15/1953'. There are now character strings and will be
compared as character strings, so '01/01/2007' will be LESS than
'02/15/1953' (since any string that begins with '01' will be less than any
string that begins with '02'). So, if your intent is to compare only the
date portion of the datetimes, then the type you want to use in the
Convert() function is 112 which generates a string of the format yyyymmdd
which will sort in the desired order.

So, something like:



"Andre" <Andre@discussions.microsoft.com> wrote in message
news:25A8487C-1022-4C4F-8462-CA549C9528EF@microsoft.com...
> Thanks for that wonderful response. Can you help me write something that
> will
> work for my situation? I am trying to basically insert into my archive
> table
> the result of the changed record from enrllmnt if the trigger criteria is
> met, but the tester2 trigger that does that keeps inserting the original
> record and not the changed record. I am not that experienced in triggers
> and
> could use your help.
>
> "Tom Cooper" wrote:
>
>> I have good news and bad news.
>>
>> The good news is that if you have more than one trigger on a table for a
>> particular action (insert, update, delete), you can specify the one that
>> should go first and/or you can specify the one that should go last. See
>> the
>> sp_settriggerorder documentation in BOL for how to do this.
>>
>> The bad news is that if you have more than one trigger on a table for a
>> particular action and one or more of those triggers updates the table the
>> trigger is for, the processing that SQL Server will do may not be what
>> you
>> expect. If you created your two triggers and set tester to fire first
>> and
>> had the default value for the database option for recursive triggers
>> (OFF)
>> and the default server option for nested triggers (ON), then SQL Server
>> would do the following when you inserted or updated rows in the ENRLLMNT
>> table.
>>
>> First the insert or update would be done.
>>
>> Second the tester trigger would fire. In the inserted table would be the
>> updated or inserted rows. If it was an update operation, the deleted
>> table
>> would have the before images of the rows. The tester trigger updates the
>> ENRLLMNT table. Because the recursive triggers option is OFF, tester is
>> not
>> fired again. But since the nested triggers option is ON, the tester2
>> trigger
>> will be fired because of this update in the tester trigger.
>>
>> Third, the tester2 trigger runs because of the update done by tester. It
>> will have the rows as updated by tester in the inserted table and will
>> insert a copy of them in the ENRLLMNT_ARCHIVE table (which I think is
>> what
>> you want done, BUT ...), now the tester2 trigger exits and to back to the
>> tester trigger and the tester trigger exits.
>>
>> Fourth, now SQL Server fires the tester2 trigger again. Remember, the
>> first
>> time tester2 ran was for the update done by tester, SQL Server still has
>> to
>> fire the tester2 trigger for the original update. So tester2 will run
>> again, and this time the rows in the inserted table will have the values
>> from the original insert or update and tester2 will now insert a copy of
>> those rows into the ENRLLMNT_ARCHIVE table.
>>
>> You can change what SQL Server does with these triggers by changing the
>> database option RECURSIVE_TRIGGERS and/or the server nested triggers
>> options. But changing the default values for these can have side effects
>> since the first affects all triggers in your database and the second
>> affects
>> all triggers in your server (or all triggers in this instance of SQL
>> Server
>> if you have multiple instances of SQL Server on your server).
>>
>> If possible, I would recommend that you write just one trigger for each
>> operation (that is, it is fine, for example, to have one trigger for
>> deletes
>> and one trigger for insert,update, but I would avoid having multiple
>> triggers for the same table for, for example, update). If you have only
>> one
>> trigger, it is easy to control the order of operations. Multiple
>> triggers
>> can do things that you don't expect.
>>
>> Tom
>>
>> "Andre" <Andre@discussions.microsoft.com> wrote in message
>> news:9F558BB3-EA64-459F-98E8-941F030FAEAF@microsoft.com...
>> >I have 2 triggers which are listed below.
>> >
>> > CREATE TRIGGER tester ON [dbo].[ENRLLMNT]
>> > FOR INSERT, UPDATE
>> > AS
>> > update enrllmnt
>> > set cntusrdateprocess = b.editdate
>> > from inserted a inner join enrllmnt b on a.dwdocid = b.dwdocid
>> > where convert(char(10), b.cntusrdateprocess,101) > convert(char(10),
>> > b.editdate,101)
>> >
>> > CREATE TRIGGER tester2 ON ENRLLMNT
>> > After INSERT, UPDATE
>> > AS
>> > INSERT INTO ENRLLMNT_ARCHIVE
>> > select *
>> > from inserted
>> >
>> > My issue is I need the trigger called tester to execute first since it
>> > is
>> > changing records if the condition is met. I want the updated record to
>> > be
>> > inserted into my archive table after that which is the trigger called
>> > tester2.
>>
>>
>>


Tom Cooper

3/23/2007 6:38:00 PM

0

Sorry, accedentially hit send.
Trigger something like:

CREATE TRIGGER tester ON [dbo].[ENRLLMNT]
FOR INSERT, UPDATE
AS

INSERT INTO ENRLLMNT_ARCHIVE (dwdocid, editdate, <list of other columns>)
select a.dwdocid, b.editdate, <other columns>
from inserted a
inner join ENRLLMNT b on a.dwdocid = b.dwdocid
where convert(char(8), b.cntusrdateprocess,112) > convert(char(8),
b.editdate,112)
IF @@ERROR <> 0
BEGIN
ROLLBACK
RETURN
END

update ENRLLMNT
set cntusrdateprocess = b.editdate
from inserted a
inner join ENRLLMNT b on a.dwdocid = b.dwdocid
where convert(char(8), b.cntusrdateprocess,112) > convert(char(8),
b.editdate,112)

IF @@ERROR <> 0
BEGIN
ROLLBACK
RETURN
END


Tom

"Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message
news:%23xabTmXbHHA.1400@TK2MSFTNGP06.phx.gbl...
>I would do something prety close to what Bob showed you. I would change a
>few things, first there is no need to do a BEGIN TRAN in a trigger,
>whenever a trigger is running, it is always in a transaction (even if the
>application didn't do a transaction, the insert/update/delete that fires a
>trigger and the execution of the fired trigger(s) are always in an implicit
>transaction. Second, I don't think you want to use type 101 in your calls
>to Convert() in the where clause. Type 101 converts the datetime to a
>character string of the format 'mm/dd/yyyy'. So Jan 1, 2007 would be
>'01/01/2007' and Feb 15, 1953 would be '02/15/1953'. There are now
>character strings and will be compared as character strings, so
>'01/01/2007' will be LESS than '02/15/1953' (since any string that begins
>with '01' will be less than any string that begins with '02'). So, if your
>intent is to compare only the date portion of the datetimes, then the type
>you want to use in the Convert() function is 112 which generates a string
>of the format yyyymmdd which will sort in the desired order.
>
> So, something like:
>
>
>
> "Andre" <Andre@discussions.microsoft.com> wrote in message
> news:25A8487C-1022-4C4F-8462-CA549C9528EF@microsoft.com...
>> Thanks for that wonderful response. Can you help me write something that
>> will
>> work for my situation? I am trying to basically insert into my archive
>> table
>> the result of the changed record from enrllmnt if the trigger criteria is
>> met, but the tester2 trigger that does that keeps inserting the original
>> record and not the changed record. I am not that experienced in triggers
>> and
>> could use your help.
>>
>> "Tom Cooper" wrote:
>>
>>> I have good news and bad news.
>>>
>>> The good news is that if you have more than one trigger on a table for a
>>> particular action (insert, update, delete), you can specify the one that
>>> should go first and/or you can specify the one that should go last. See
>>> the
>>> sp_settriggerorder documentation in BOL for how to do this.
>>>
>>> The bad news is that if you have more than one trigger on a table for a
>>> particular action and one or more of those triggers updates the table
>>> the
>>> trigger is for, the processing that SQL Server will do may not be what
>>> you
>>> expect. If you created your two triggers and set tester to fire first
>>> and
>>> had the default value for the database option for recursive triggers
>>> (OFF)
>>> and the default server option for nested triggers (ON), then SQL Server
>>> would do the following when you inserted or updated rows in the ENRLLMNT
>>> table.
>>>
>>> First the insert or update would be done.
>>>
>>> Second the tester trigger would fire. In the inserted table would be
>>> the
>>> updated or inserted rows. If it was an update operation, the deleted
>>> table
>>> would have the before images of the rows. The tester trigger updates
>>> the
>>> ENRLLMNT table. Because the recursive triggers option is OFF, tester is
>>> not
>>> fired again. But since the nested triggers option is ON, the tester2
>>> trigger
>>> will be fired because of this update in the tester trigger.
>>>
>>> Third, the tester2 trigger runs because of the update done by tester.
>>> It
>>> will have the rows as updated by tester in the inserted table and will
>>> insert a copy of them in the ENRLLMNT_ARCHIVE table (which I think is
>>> what
>>> you want done, BUT ...), now the tester2 trigger exits and to back to
>>> the
>>> tester trigger and the tester trigger exits.
>>>
>>> Fourth, now SQL Server fires the tester2 trigger again. Remember, the
>>> first
>>> time tester2 ran was for the update done by tester, SQL Server still has
>>> to
>>> fire the tester2 trigger for the original update. So tester2 will run
>>> again, and this time the rows in the inserted table will have the values
>>> from the original insert or update and tester2 will now insert a copy of
>>> those rows into the ENRLLMNT_ARCHIVE table.
>>>
>>> You can change what SQL Server does with these triggers by changing the
>>> database option RECURSIVE_TRIGGERS and/or the server nested triggers
>>> options. But changing the default values for these can have side
>>> effects
>>> since the first affects all triggers in your database and the second
>>> affects
>>> all triggers in your server (or all triggers in this instance of SQL
>>> Server
>>> if you have multiple instances of SQL Server on your server).
>>>
>>> If possible, I would recommend that you write just one trigger for each
>>> operation (that is, it is fine, for example, to have one trigger for
>>> deletes
>>> and one trigger for insert,update, but I would avoid having multiple
>>> triggers for the same table for, for example, update). If you have only
>>> one
>>> trigger, it is easy to control the order of operations. Multiple
>>> triggers
>>> can do things that you don't expect.
>>>
>>> Tom
>>>
>>> "Andre" <Andre@discussions.microsoft.com> wrote in message
>>> news:9F558BB3-EA64-459F-98E8-941F030FAEAF@microsoft.com...
>>> >I have 2 triggers which are listed below.
>>> >
>>> > CREATE TRIGGER tester ON [dbo].[ENRLLMNT]
>>> > FOR INSERT, UPDATE
>>> > AS
>>> > update enrllmnt
>>> > set cntusrdateprocess = b.editdate
>>> > from inserted a inner join enrllmnt b on a.dwdocid = b.dwdocid
>>> > where convert(char(10), b.cntusrdateprocess,101) > convert(char(10),
>>> > b.editdate,101)
>>> >
>>> > CREATE TRIGGER tester2 ON ENRLLMNT
>>> > After INSERT, UPDATE
>>> > AS
>>> > INSERT INTO ENRLLMNT_ARCHIVE
>>> > select *
>>> > from inserted
>>> >
>>> > My issue is I need the trigger called tester to execute first since it
>>> > is
>>> > changing records if the condition is met. I want the updated record to
>>> > be
>>> > inserted into my archive table after that which is the trigger called
>>> > tester2.
>>>
>>>
>>>
>
>


Andre

3/24/2007 12:12:00 AM

0

Ok so what happens in this situation.

insert into enrllmnt (dwdocid, dwpagecount, cntusrdateprocess, editdate)
values (4912983, 3, '03/20/2007', '03/21/2007')

The insert into my archive table will not take place and I need an archive
record everytime.

"Tom Cooper" wrote:

> Sorry, accedentially hit send.
> Trigger something like:
>
> CREATE TRIGGER tester ON [dbo].[ENRLLMNT]
> FOR INSERT, UPDATE
> AS
>
> INSERT INTO ENRLLMNT_ARCHIVE (dwdocid, editdate, <list of other columns>)
> select a.dwdocid, b.editdate, <other columns>
> from inserted a
> inner join ENRLLMNT b on a.dwdocid = b.dwdocid
> where convert(char(8), b.cntusrdateprocess,112) > convert(char(8),
> b.editdate,112)
> IF @@ERROR <> 0
> BEGIN
> ROLLBACK
> RETURN
> END
>
> update ENRLLMNT
> set cntusrdateprocess = b.editdate
> from inserted a
> inner join ENRLLMNT b on a.dwdocid = b.dwdocid
> where convert(char(8), b.cntusrdateprocess,112) > convert(char(8),
> b.editdate,112)
>
> IF @@ERROR <> 0
> BEGIN
> ROLLBACK
> RETURN
> END
>
>
> Tom
>
> "Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message
> news:%23xabTmXbHHA.1400@TK2MSFTNGP06.phx.gbl...
> >I would do something prety close to what Bob showed you. I would change a
> >few things, first there is no need to do a BEGIN TRAN in a trigger,
> >whenever a trigger is running, it is always in a transaction (even if the
> >application didn't do a transaction, the insert/update/delete that fires a
> >trigger and the execution of the fired trigger(s) are always in an implicit
> >transaction. Second, I don't think you want to use type 101 in your calls
> >to Convert() in the where clause. Type 101 converts the datetime to a
> >character string of the format 'mm/dd/yyyy'. So Jan 1, 2007 would be
> >'01/01/2007' and Feb 15, 1953 would be '02/15/1953'. There are now
> >character strings and will be compared as character strings, so
> >'01/01/2007' will be LESS than '02/15/1953' (since any string that begins
> >with '01' will be less than any string that begins with '02'). So, if your
> >intent is to compare only the date portion of the datetimes, then the type
> >you want to use in the Convert() function is 112 which generates a string
> >of the format yyyymmdd which will sort in the desired order.
> >
> > So, something like:
> >
> >
> >
> > "Andre" <Andre@discussions.microsoft.com> wrote in message
> > news:25A8487C-1022-4C4F-8462-CA549C9528EF@microsoft.com...
> >> Thanks for that wonderful response. Can you help me write something that
> >> will
> >> work for my situation? I am trying to basically insert into my archive
> >> table
> >> the result of the changed record from enrllmnt if the trigger criteria is
> >> met, but the tester2 trigger that does that keeps inserting the original
> >> record and not the changed record. I am not that experienced in triggers
> >> and
> >> could use your help.
> >>
> >> "Tom Cooper" wrote:
> >>
> >>> I have good news and bad news.
> >>>
> >>> The good news is that if you have more than one trigger on a table for a
> >>> particular action (insert, update, delete), you can specify the one that
> >>> should go first and/or you can specify the one that should go last. See
> >>> the
> >>> sp_settriggerorder documentation in BOL for how to do this.
> >>>
> >>> The bad news is that if you have more than one trigger on a table for a
> >>> particular action and one or more of those triggers updates the table
> >>> the
> >>> trigger is for, the processing that SQL Server will do may not be what
> >>> you
> >>> expect. If you created your two triggers and set tester to fire first
> >>> and
> >>> had the default value for the database option for recursive triggers
> >>> (OFF)
> >>> and the default server option for nested triggers (ON), then SQL Server
> >>> would do the following when you inserted or updated rows in the ENRLLMNT
> >>> table.
> >>>
> >>> First the insert or update would be done.
> >>>
> >>> Second the tester trigger would fire. In the inserted table would be
> >>> the
> >>> updated or inserted rows. If it was an update operation, the deleted
> >>> table
> >>> would have the before images of the rows. The tester trigger updates
> >>> the
> >>> ENRLLMNT table. Because the recursive triggers option is OFF, tester is
> >>> not
> >>> fired again. But since the nested triggers option is ON, the tester2
> >>> trigger
> >>> will be fired because of this update in the tester trigger.
> >>>
> >>> Third, the tester2 trigger runs because of the update done by tester.
> >>> It
> >>> will have the rows as updated by tester in the inserted table and will
> >>> insert a copy of them in the ENRLLMNT_ARCHIVE table (which I think is
> >>> what
> >>> you want done, BUT ...), now the tester2 trigger exits and to back to
> >>> the
> >>> tester trigger and the tester trigger exits.
> >>>
> >>> Fourth, now SQL Server fires the tester2 trigger again. Remember, the
> >>> first
> >>> time tester2 ran was for the update done by tester, SQL Server still has
> >>> to
> >>> fire the tester2 trigger for the original update. So tester2 will run
> >>> again, and this time the rows in the inserted table will have the values
> >>> from the original insert or update and tester2 will now insert a copy of
> >>> those rows into the ENRLLMNT_ARCHIVE table.
> >>>
> >>> You can change what SQL Server does with these triggers by changing the
> >>> database option RECURSIVE_TRIGGERS and/or the server nested triggers
> >>> options. But changing the default values for these can have side
> >>> effects
> >>> since the first affects all triggers in your database and the second
> >>> affects
> >>> all triggers in your server (or all triggers in this instance of SQL
> >>> Server
> >>> if you have multiple instances of SQL Server on your server).
> >>>
> >>> If possible, I would recommend that you write just one trigger for each
> >>> operation (that is, it is fine, for example, to have one trigger for
> >>> deletes
> >>> and one trigger for insert,update, but I would avoid having multiple
> >>> triggers for the same table for, for example, update). If you have only
> >>> one
> >>> trigger, it is easy to control the order of operations. Multiple
> >>> triggers
> >>> can do things that you don't expect.
> >>>
> >>> Tom
> >>>
> >>> "Andre" <Andre@discussions.microsoft.com> wrote in message
> >>> news:9F558BB3-EA64-459F-98E8-941F030FAEAF@microsoft.com...
> >>> >I have 2 triggers which are listed below.
> >>> >
> >>> > CREATE TRIGGER tester ON [dbo].[ENRLLMNT]
> >>> > FOR INSERT, UPDATE
> >>> > AS
> >>> > update enrllmnt
> >>> > set cntusrdateprocess = b.editdate
> >>> > from inserted a inner join enrllmnt b on a.dwdocid = b.dwdocid
> >>> > where convert(char(10), b.cntusrdateprocess,101) > convert(char(10),
> >>> > b.editdate,101)
> >>> >
> >>> > CREATE TRIGGER tester2 ON ENRLLMNT
> >>> > After INSERT, UPDATE
> >>> > AS
> >>> > INSERT INTO ENRLLMNT_ARCHIVE
> >>> > select *
> >>> > from inserted
> >>> >
> >>> > My issue is I need the trigger called tester to execute first since it
> >>> > is
> >>> > changing records if the condition is met. I want the updated record to
> >>> > be
> >>> > inserted into my archive table after that which is the trigger called
> >>> > tester2.
> >>>
> >>>
> >>>
> >
> >
>
>
>

Tom Cooper

3/24/2007 12:42:00 AM

0

Good catch. Try

CREATE TRIGGER tester ON [dbo].[ENRLLMNT]
FOR INSERT, UPDATE
AS

INSERT INTO ENRLLMNT_ARCHIVE (dwdocid, editdate, <list of other columns>)
select a.dwdocid,
CASE WHEN convert(char(8), b.cntusrdateprocess,112) > convert(char(8),
b.editdate,112)
THEN b.editdate ELSE a.cntusrdateprocess END,
<other columns>
from inserted a
inner join ENRLLMNT b on a.dwdocid = b.dwdocid
IF @@ERROR <> 0
BEGIN
ROLLBACK
RETURN
END

update ENRLLMNT
set cntusrdateprocess = b.editdate
from inserted a
inner join ENRLLMNT b on a.dwdocid = b.dwdocid
where convert(char(8), b.cntusrdateprocess,112) > convert(char(8),
b.editdate,112)

IF @@ERROR <> 0
BEGIN
ROLLBACK
RETURN
END


"Andre" <Andre@discussions.microsoft.com> wrote in message
news:0F62A7F4-071F-4F49-9C77-C7ED245CACD3@microsoft.com...
> Ok so what happens in this situation.
>
> insert into enrllmnt (dwdocid, dwpagecount, cntusrdateprocess, editdate)
> values (4912983, 3, '03/20/2007', '03/21/2007')
>
> The insert into my archive table will not take place and I need an archive
> record everytime.
>
> "Tom Cooper" wrote:
>
>> Sorry, accedentially hit send.
>> Trigger something like:
>>
>> CREATE TRIGGER tester ON [dbo].[ENRLLMNT]
>> FOR INSERT, UPDATE
>> AS
>>
>> INSERT INTO ENRLLMNT_ARCHIVE (dwdocid, editdate, <list of other columns>)
>> select a.dwdocid, b.editdate, <other columns>
>> from inserted a
>> inner join ENRLLMNT b on a.dwdocid = b.dwdocid
>> where convert(char(8), b.cntusrdateprocess,112) > convert(char(8),
>> b.editdate,112)
>> IF @@ERROR <> 0
>> BEGIN
>> ROLLBACK
>> RETURN
>> END
>>
>> update ENRLLMNT
>> set cntusrdateprocess = b.editdate
>> from inserted a
>> inner join ENRLLMNT b on a.dwdocid = b.dwdocid
>> where convert(char(8), b.cntusrdateprocess,112) > convert(char(8),
>> b.editdate,112)
>>
>> IF @@ERROR <> 0
>> BEGIN
>> ROLLBACK
>> RETURN
>> END
>>
>>
>> Tom
>>
>> "Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message
>> news:%23xabTmXbHHA.1400@TK2MSFTNGP06.phx.gbl...
>> >I would do something prety close to what Bob showed you. I would change
>> >a
>> >few things, first there is no need to do a BEGIN TRAN in a trigger,
>> >whenever a trigger is running, it is always in a transaction (even if
>> >the
>> >application didn't do a transaction, the insert/update/delete that fires
>> >a
>> >trigger and the execution of the fired trigger(s) are always in an
>> >implicit
>> >transaction. Second, I don't think you want to use type 101 in your
>> >calls
>> >to Convert() in the where clause. Type 101 converts the datetime to a
>> >character string of the format 'mm/dd/yyyy'. So Jan 1, 2007 would be
>> >'01/01/2007' and Feb 15, 1953 would be '02/15/1953'. There are now
>> >character strings and will be compared as character strings, so
>> >'01/01/2007' will be LESS than '02/15/1953' (since any string that
>> >begins
>> >with '01' will be less than any string that begins with '02'). So, if
>> >your
>> >intent is to compare only the date portion of the datetimes, then the
>> >type
>> >you want to use in the Convert() function is 112 which generates a
>> >string
>> >of the format yyyymmdd which will sort in the desired order.
>> >
>> > So, something like:
>> >
>> >
>> >
>> > "Andre" <Andre@discussions.microsoft.com> wrote in message
>> > news:25A8487C-1022-4C4F-8462-CA549C9528EF@microsoft.com...
>> >> Thanks for that wonderful response. Can you help me write something
>> >> that
>> >> will
>> >> work for my situation? I am trying to basically insert into my archive
>> >> table
>> >> the result of the changed record from enrllmnt if the trigger criteria
>> >> is
>> >> met, but the tester2 trigger that does that keeps inserting the
>> >> original
>> >> record and not the changed record. I am not that experienced in
>> >> triggers
>> >> and
>> >> could use your help.
>> >>
>> >> "Tom Cooper" wrote:
>> >>
>> >>> I have good news and bad news.
>> >>>
>> >>> The good news is that if you have more than one trigger on a table
>> >>> for a
>> >>> particular action (insert, update, delete), you can specify the one
>> >>> that
>> >>> should go first and/or you can specify the one that should go last.
>> >>> See
>> >>> the
>> >>> sp_settriggerorder documentation in BOL for how to do this.
>> >>>
>> >>> The bad news is that if you have more than one trigger on a table for
>> >>> a
>> >>> particular action and one or more of those triggers updates the table
>> >>> the
>> >>> trigger is for, the processing that SQL Server will do may not be
>> >>> what
>> >>> you
>> >>> expect. If you created your two triggers and set tester to fire
>> >>> first
>> >>> and
>> >>> had the default value for the database option for recursive triggers
>> >>> (OFF)
>> >>> and the default server option for nested triggers (ON), then SQL
>> >>> Server
>> >>> would do the following when you inserted or updated rows in the
>> >>> ENRLLMNT
>> >>> table.
>> >>>
>> >>> First the insert or update would be done.
>> >>>
>> >>> Second the tester trigger would fire. In the inserted table would be
>> >>> the
>> >>> updated or inserted rows. If it was an update operation, the deleted
>> >>> table
>> >>> would have the before images of the rows. The tester trigger updates
>> >>> the
>> >>> ENRLLMNT table. Because the recursive triggers option is OFF, tester
>> >>> is
>> >>> not
>> >>> fired again. But since the nested triggers option is ON, the tester2
>> >>> trigger
>> >>> will be fired because of this update in the tester trigger.
>> >>>
>> >>> Third, the tester2 trigger runs because of the update done by tester.
>> >>> It
>> >>> will have the rows as updated by tester in the inserted table and
>> >>> will
>> >>> insert a copy of them in the ENRLLMNT_ARCHIVE table (which I think is
>> >>> what
>> >>> you want done, BUT ...), now the tester2 trigger exits and to back to
>> >>> the
>> >>> tester trigger and the tester trigger exits.
>> >>>
>> >>> Fourth, now SQL Server fires the tester2 trigger again. Remember,
>> >>> the
>> >>> first
>> >>> time tester2 ran was for the update done by tester, SQL Server still
>> >>> has
>> >>> to
>> >>> fire the tester2 trigger for the original update. So tester2 will
>> >>> run
>> >>> again, and this time the rows in the inserted table will have the
>> >>> values
>> >>> from the original insert or update and tester2 will now insert a copy
>> >>> of
>> >>> those rows into the ENRLLMNT_ARCHIVE table.
>> >>>
>> >>> You can change what SQL Server does with these triggers by changing
>> >>> the
>> >>> database option RECURSIVE_TRIGGERS and/or the server nested triggers
>> >>> options. But changing the default values for these can have side
>> >>> effects
>> >>> since the first affects all triggers in your database and the second
>> >>> affects
>> >>> all triggers in your server (or all triggers in this instance of SQL
>> >>> Server
>> >>> if you have multiple instances of SQL Server on your server).
>> >>>
>> >>> If possible, I would recommend that you write just one trigger for
>> >>> each
>> >>> operation (that is, it is fine, for example, to have one trigger for
>> >>> deletes
>> >>> and one trigger for insert,update, but I would avoid having multiple
>> >>> triggers for the same table for, for example, update). If you have
>> >>> only
>> >>> one
>> >>> trigger, it is easy to control the order of operations. Multiple
>> >>> triggers
>> >>> can do things that you don't expect.
>> >>>
>> >>> Tom
>> >>>
>> >>> "Andre" <Andre@discussions.microsoft.com> wrote in message
>> >>> news:9F558BB3-EA64-459F-98E8-941F030FAEAF@microsoft.com...
>> >>> >I have 2 triggers which are listed below.
>> >>> >
>> >>> > CREATE TRIGGER tester ON [dbo].[ENRLLMNT]
>> >>> > FOR INSERT, UPDATE
>> >>> > AS
>> >>> > update enrllmnt
>> >>> > set cntusrdateprocess = b.editdate
>> >>> > from inserted a inner join enrllmnt b on a.dwdocid = b.dwdocid
>> >>> > where convert(char(10), b.cntusrdateprocess,101) >
>> >>> > convert(char(10),
>> >>> > b.editdate,101)
>> >>> >
>> >>> > CREATE TRIGGER tester2 ON ENRLLMNT
>> >>> > After INSERT, UPDATE
>> >>> > AS
>> >>> > INSERT INTO ENRLLMNT_ARCHIVE
>> >>> > select *
>> >>> > from inserted
>> >>> >
>> >>> > My issue is I need the trigger called tester to execute first since
>> >>> > it
>> >>> > is
>> >>> > changing records if the condition is met. I want the updated record
>> >>> > to
>> >>> > be
>> >>> > inserted into my archive table after that which is the trigger
>> >>> > called
>> >>> > tester2.
>> >>>
>> >>>
>> >>>
>> >
>> >
>>
>>
>>


Andre

3/24/2007 4:35:00 AM

0

Well you are a genius. I cant say how much I appreciate your help.

"Tom Cooper" wrote:

> Good catch. Try
>
> CREATE TRIGGER tester ON [dbo].[ENRLLMNT]
> FOR INSERT, UPDATE
> AS
>
> INSERT INTO ENRLLMNT_ARCHIVE (dwdocid, editdate, <list of other columns>)
> select a.dwdocid,
> CASE WHEN convert(char(8), b.cntusrdateprocess,112) > convert(char(8),
> b.editdate,112)
> THEN b.editdate ELSE a.cntusrdateprocess END,
> <other columns>
> from inserted a
> inner join ENRLLMNT b on a.dwdocid = b.dwdocid
> IF @@ERROR <> 0
> BEGIN
> ROLLBACK
> RETURN
> END
>
> update ENRLLMNT
> set cntusrdateprocess = b.editdate
> from inserted a
> inner join ENRLLMNT b on a.dwdocid = b.dwdocid
> where convert(char(8), b.cntusrdateprocess,112) > convert(char(8),
> b.editdate,112)
>
> IF @@ERROR <> 0
> BEGIN
> ROLLBACK
> RETURN
> END
>
>
> "Andre" <Andre@discussions.microsoft.com> wrote in message
> news:0F62A7F4-071F-4F49-9C77-C7ED245CACD3@microsoft.com...
> > Ok so what happens in this situation.
> >
> > insert into enrllmnt (dwdocid, dwpagecount, cntusrdateprocess, editdate)
> > values (4912983, 3, '03/20/2007', '03/21/2007')
> >
> > The insert into my archive table will not take place and I need an archive
> > record everytime.
> >
> > "Tom Cooper" wrote:
> >
> >> Sorry, accedentially hit send.
> >> Trigger something like:
> >>
> >> CREATE TRIGGER tester ON [dbo].[ENRLLMNT]
> >> FOR INSERT, UPDATE
> >> AS
> >>
> >> INSERT INTO ENRLLMNT_ARCHIVE (dwdocid, editdate, <list of other columns>)
> >> select a.dwdocid, b.editdate, <other columns>
> >> from inserted a
> >> inner join ENRLLMNT b on a.dwdocid = b.dwdocid
> >> where convert(char(8), b.cntusrdateprocess,112) > convert(char(8),
> >> b.editdate,112)
> >> IF @@ERROR <> 0
> >> BEGIN
> >> ROLLBACK
> >> RETURN
> >> END
> >>
> >> update ENRLLMNT
> >> set cntusrdateprocess = b.editdate
> >> from inserted a
> >> inner join ENRLLMNT b on a.dwdocid = b.dwdocid
> >> where convert(char(8), b.cntusrdateprocess,112) > convert(char(8),
> >> b.editdate,112)
> >>
> >> IF @@ERROR <> 0
> >> BEGIN
> >> ROLLBACK
> >> RETURN
> >> END
> >>
> >>
> >> Tom
> >>
> >> "Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message
> >> news:%23xabTmXbHHA.1400@TK2MSFTNGP06.phx.gbl...
> >> >I would do something prety close to what Bob showed you. I would change
> >> >a
> >> >few things, first there is no need to do a BEGIN TRAN in a trigger,
> >> >whenever a trigger is running, it is always in a transaction (even if
> >> >the
> >> >application didn't do a transaction, the insert/update/delete that fires
> >> >a
> >> >trigger and the execution of the fired trigger(s) are always in an
> >> >implicit
> >> >transaction. Second, I don't think you want to use type 101 in your
> >> >calls
> >> >to Convert() in the where clause. Type 101 converts the datetime to a
> >> >character string of the format 'mm/dd/yyyy'. So Jan 1, 2007 would be
> >> >'01/01/2007' and Feb 15, 1953 would be '02/15/1953'. There are now
> >> >character strings and will be compared as character strings, so
> >> >'01/01/2007' will be LESS than '02/15/1953' (since any string that
> >> >begins
> >> >with '01' will be less than any string that begins with '02'). So, if
> >> >your
> >> >intent is to compare only the date portion of the datetimes, then the
> >> >type
> >> >you want to use in the Convert() function is 112 which generates a
> >> >string
> >> >of the format yyyymmdd which will sort in the desired order.
> >> >
> >> > So, something like:
> >> >
> >> >
> >> >
> >> > "Andre" <Andre@discussions.microsoft.com> wrote in message
> >> > news:25A8487C-1022-4C4F-8462-CA549C9528EF@microsoft.com...
> >> >> Thanks for that wonderful response. Can you help me write something
> >> >> that
> >> >> will
> >> >> work for my situation? I am trying to basically insert into my archive
> >> >> table
> >> >> the result of the changed record from enrllmnt if the trigger criteria
> >> >> is
> >> >> met, but the tester2 trigger that does that keeps inserting the
> >> >> original
> >> >> record and not the changed record. I am not that experienced in
> >> >> triggers
> >> >> and
> >> >> could use your help.
> >> >>
> >> >> "Tom Cooper" wrote:
> >> >>
> >> >>> I have good news and bad news.
> >> >>>
> >> >>> The good news is that if you have more than one trigger on a table
> >> >>> for a
> >> >>> particular action (insert, update, delete), you can specify the one
> >> >>> that
> >> >>> should go first and/or you can specify the one that should go last.
> >> >>> See
> >> >>> the
> >> >>> sp_settriggerorder documentation in BOL for how to do this.
> >> >>>
> >> >>> The bad news is that if you have more than one trigger on a table for
> >> >>> a
> >> >>> particular action and one or more of those triggers updates the table
> >> >>> the
> >> >>> trigger is for, the processing that SQL Server will do may not be
> >> >>> what
> >> >>> you
> >> >>> expect. If you created your two triggers and set tester to fire
> >> >>> first
> >> >>> and
> >> >>> had the default value for the database option for recursive triggers
> >> >>> (OFF)
> >> >>> and the default server option for nested triggers (ON), then SQL
> >> >>> Server
> >> >>> would do the following when you inserted or updated rows in the
> >> >>> ENRLLMNT
> >> >>> table.
> >> >>>
> >> >>> First the insert or update would be done.
> >> >>>
> >> >>> Second the tester trigger would fire. In the inserted table would be
> >> >>> the
> >> >>> updated or inserted rows. If it was an update operation, the deleted
> >> >>> table
> >> >>> would have the before images of the rows. The tester trigger updates
> >> >>> the
> >> >>> ENRLLMNT table. Because the recursive triggers option is OFF, tester
> >> >>> is
> >> >>> not
> >> >>> fired again. But since the nested triggers option is ON, the tester2
> >> >>> trigger
> >> >>> will be fired because of this update in the tester trigger.
> >> >>>
> >> >>> Third, the tester2 trigger runs because of the update done by tester.
> >> >>> It
> >> >>> will have the rows as updated by tester in the inserted table and
> >> >>> will
> >> >>> insert a copy of them in the ENRLLMNT_ARCHIVE table (which I think is
> >> >>> what
> >> >>> you want done, BUT ...), now the tester2 trigger exits and to back to
> >> >>> the
> >> >>> tester trigger and the tester trigger exits.
> >> >>>
> >> >>> Fourth, now SQL Server fires the tester2 trigger again. Remember,
> >> >>> the
> >> >>> first
> >> >>> time tester2 ran was for the update done by tester, SQL Server still
> >> >>> has
> >> >>> to
> >> >>> fire the tester2 trigger for the original update. So tester2 will
> >> >>> run
> >> >>> again, and this time the rows in the inserted table will have the
> >> >>> values
> >> >>> from the original insert or update and tester2 will now insert a copy
> >> >>> of
> >> >>> those rows into the ENRLLMNT_ARCHIVE table.
> >> >>>
> >> >>> You can change what SQL Server does with these triggers by changing
> >> >>> the
> >> >>> database option RECURSIVE_TRIGGERS and/or the server nested triggers
> >> >>> options. But changing the default values for these can have side
> >> >>> effects
> >> >>> since the first affects all triggers in your database and the second
> >> >>> affects
> >> >>> all triggers in your server (or all triggers in this instance of SQL
> >> >>> Server
> >> >>> if you have multiple instances of SQL Server on your server).
> >> >>>
> >> >>> If possible, I would recommend that you write just one trigger for
> >> >>> each
> >> >>> operation (that is, it is fine, for example, to have one trigger for
> >> >>> deletes
> >> >>> and one trigger for insert,update, but I would avoid having multiple
> >> >>> triggers for the same table for, for example, update). If you have
> >> >>> only
> >> >>> one
> >> >>> trigger, it is easy to control the order of operations. Multiple
> >> >>> triggers
> >> >>> can do things that you don't expect.
> >> >>>
> >> >>> Tom
> >> >>>
> >> >>> "Andre" <Andre@discussions.microsoft.com> wrote in message
> >> >>> news:9F558BB3-EA64-459F-98E8-941F030FAEAF@microsoft.com...
> >> >>> >I have 2 triggers which are listed below.
> >> >>> >
> >> >>> > CREATE TRIGGER tester ON [dbo].[ENRLLMNT]
> >> >>> > FOR INSERT, UPDATE
> >> >>> > AS
> >> >>> > update enrllmnt
> >> >>> > set cntusrdateprocess = b.editdate
> >> >>> > from inserted a inner join enrllmnt b on a.dwdocid = b.dwdocid
> >> >>> > where convert(char(10), b.cntusrdateprocess,101) >
> >> >>> > convert(char(10),
> >> >>> > b.editdate,101)
> >> >>> >
> >> >>> > CREATE TRIGGER tester2 ON ENRLLMNT
> >> >>> > After INSERT, UPDATE
> >> >>> > AS
> >> >>> > INSERT INTO ENRLLMNT_ARCHIVE
> >> >>> > select *
> >> >>> > from inserted
> >> >>> >
> >> >>> > My issue is I need the trigger called tester to execute first since
> >> >>> > it
> >> >>> > is
> >> >>> > changing records if the condition is met. I want the updated record
> >> >>> > to
> >> >>> > be
> >> >>> > inserted into my archive table after that which is the trigger
> >> >>> > called
> >> >>> > tester2.
> >> >>>
> >> >>>
> >> >>>
> >> >
> >> >
> >>
> >>
> >>
>
>
>