February 26, 2010 at 4:33 am
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.
February 26, 2010 at 11:35 am
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'
February 28, 2010 at 11:16 pm
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.
February 28, 2010 at 11:55 pm
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
March 2, 2010 at 7:25 am
The problem was solved by declaring few variables and using select into statement. Thanks for help...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply