Trigger

Question

Post reply

Trigger

einoris

SSC Enthusiast

Points: 123
More actions
February 26, 2010 at 4:33 am

#219451

Hello, I'm newbie in T-SQL. I need to create a trigger which updates field on one table from another table. For Example:
UPDATE Table1
SET table1.parentrefrecid = (select Table2.recid from Table2
where Table2.bookingid=Table1.bookingid and Table2.bookingid='xk0022369'
and Table2.dataareaid='200')
where exists (select Table2.recid from Table2
where Table2.bookingid=Table1.bookingid and Table2.bookingid='xk0022369'
and Table2.dataareaid='200')
I've tried to create trigger whick looks like this, but I get an error when inserting records to table1:
CREATE TRIGGER UpdateRecID AFTER INSERT
ON Table1
Begin
UPDATE Table1
SET parentrefrecid = ((select Table2.recid from Table2
where Table2.bookingid=Table1.bookingid and Table2.bookingid='xk0022369'
and Table2.dataareaid='200')
where exists (select Table2.recid from Table2
where Table2.bookingid=Table1.bookingid and Table2.bookingid='xk0022369'
and Table2.dataareaid='200'))
End;
PS I am working with SQL Oracle Developer. Please help me.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Toby White SSCertifiable Points: 6187 More actions · Answer 1

Update t1

SET t1.Parentrefrecid = t2.recid

FROM Table1 t1

Join Table2 t2 on t1.bookingid = t2.bookingid

WHERE t1.bookingid = inserted.bookingid

AND t2.dataareaid = '200'

einoris SSC Enthusiast Points: 123 More actions · Answer 2

Thanks Toby for your reply but still there's no luck. The folowing statement has been ignored:

Set t1.parentrefrecid=t2.recid

And on join i got error as well --> sql command not properly ended.

Lowell SSC Guru Points: 323482 More actions · Answer 3

here's my best guess;

if you are going to update the main table, you need to update it from the virtual table INSERTED, which might be joined to other tables to get the data, ie Table2 (i hate pseudo code! Table2? it's not really named that, is it?) if bookingid is not the PK of the table, you need to change the WHERE statement for the joining of INSERTED to Table1.

since you didn't post the table structures, this is just an intelligent but wild guess.

CREATE TRIGGER UpdateRecID ON Table1

AFTER INSERT

AS

Begin

UPDATE Table1

SET parentrefrecid = Table2.recid

from INSERTED

INNER JOIN Table2

ON Table2.bookingid=INSERTED.bookingid

and Table2.dataareaid='200'

where Table1.bookingid = INSERTED.bookingid

End;

also, it seems like the parentrefrecid can be figured out based on the joins...seems a waste to create a trigger to update it, when you could use a view to get teh value on demand instead. I would consider replacing the trigger with a view that joins the two tables instead to get the data you were looking for.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

einoris SSC Enthusiast Points: 123 More actions · Answer 4

The problem was solved by declaring few variables and using select into statement. Thanks for help...