Help with a Trigger!

[ptmcs]

Hello all,

We have an application (ambercat - helpdesk software)
whereby an action can be added to a request which
results in the following sql:

begin transaction
insert into Actions
update requests
commit


What I want to do is add a trigger to the Actions table
which will do up updates (including the Requests table)
- that I would ideally! like to happen after the above commit has been done....
but obviously adding the trigger to the actions table
results in anything in the trigger happenning before
the commit is reached.....

Any ideas anyone?

Perhaps the answer is to write a flag to another table,
which a polling process monitors and then does the
required updates the trigger would have done.....?

 

[LloydDobler]

How about a stored procedure to run after the commit?

Al

"Be quiet Brain, or I'll stab you with a Q-tip!" (Homer Simpson)

 

[ptmcs]

we cannot get into the application code, so would not
be able to add a stored procedure after the commit.
but we can alter the database, ie: add a trigger -

 

[jgoodman00]

Perhaps the answer is to write a flag to another table,
which a polling process monitors and then does the
required updates the trigger would have done.....?

Because you say you want it to run after the commit & you cannot change the calling application I think you will be stuck with this idea...

James Goodman MCSE, MCDBA

http://www.j.goodman.dsl.pipex.com

 

[557]

what if you change the conditions given in the trigger which will affect when it is triggered

 

[maswien]

If the insert for table action is not commited, I don't think the trigger on this table will be fired, so add a trigger will do it.

 

[SQLSister]

When a trigger is triggered cannot be changed. If it is an Update trigger, it is triggered after every update. If it is an insert trigger it will happen after every insert.

Questions about posting. See faq183-874

 

[LloydDobler]

Why does it matter if a trigger fires before or after the commit?

Al

"Be quiet Brain, or I'll stab you with a Q-tip!" (Homer Simpson)

 

[maswien]

Try following to see what will happen:

First, create a trigger,

CREATE TRIGGER test
ON tmonth
FOR INSERT
AS
select * into rmonth from inserted

Then, start running a transaction,

begin transaction
insert into tmonth values(12, 'dec')
WAITFOR TIME '10:09'
commit

Then run following sql,

select * from rmonth

You won't get answer from rmonth until the transaction is commited.