trigger stuff - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

trigger stuff

Author

Topic

yonabout
Posting Yak Master

112 Posts

Posted - 2007-02-19 : 12:25:35

Hi,

I've got a table that gets written to.

Each time a line is inserted, I want to do a check, then replace one of the values thats getting inserted with a value that I've picked.

So if table ABC has cols A, B and C, and 'A', 'B' and 'C' are the values getting inserted, I want to do something like this (please forgive the pseudo SQL):

create trigger ABCUpdate on ABC
for insert

declare 

set @variable = (select C from inserted)
if @variable  = 'C'
set @variable = 'D'

insert ABC inserted.A, inserted.B, @variable

In effect, I want to change one of the values thats getting inserted, basically because I don't have access to the object thats populating the database, which isn't working correctly.

Anyway, its all good up to the bit where I try to insert the new values into the table thats getting inserted into, then it breaks.

Do I need to use an instead trigger?

Cheers,

Yonabout

Kristen
Test

22859 Posts

Posted - 2007-02-19 : 12:51:55

You want to UPDATE what will, by the time the trigger fires, already be in your ABC table, don't you?

If so the guts of your trigger will look something like:


UPDATE U
SET [MyColumn] = CASE WHEN I.MyColumn = 'C' THEN 'D' ELSE I.MyColumnC END
FROM inserted AS I
     JOIN dbo.ABC AS U
         ON U.MyPK = I=.MyPK

Kristen

yonabout
Posting Yak Master

112 Posts

Posted - 2007-02-20 : 06:03:01

I think so?

I want to update the stuff in the temporary "inserted" table before it gets inserted.

Is my understanding of triggers off? I thought that an insert trigger captures the data before its been written to the table.

is trigger code fired after the stuff has been inserted?

Cheers,

Yonabout

Kristen
Test

22859 Posts

Posted - 2007-02-20 : 07:35:51

"I want to update the stuff in the temporary "inserted" table before it gets inserted."

By default Triggers are "AFTER", and the data is already inserted by the time the trigger fires (but you can use an INSTEAD OF trigger, they are more work to set up and maintain though); I suggest you just update the table itself (i.e. a second time!)

(You can't update the "inserted" pseudo table)

If your trigger discovers that the data should NOT have been inserted it can do a ROLLBACK to kill the process (which will reverse the INSERT that has already been made to the table)

Kristen

yonabout
Posting Yak Master

112 Posts

Posted - 2007-02-20 : 08:19:30

OK Thanks,

I had read "an introduction to triggers part I" on this website where it said

quote:
The inserted table contains the data referenced in an INSERT before it is actually committed to the database

I understood this to be "A trigger is fired before the data has hit the table" which is why I was trying to alter it "In transit".

So the process is actually:
Data is written to database / table
Trigger code is executed
Data is comitted to database

which makes much more sense.

Thanks for clarifying!

Cheers,

Yonabout

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources