Help with a Trigger

[nathanharcup]

Hello All,
I hope someone can advise me what I am doing wrong with this trigger. I have a web application that is hard coded to write a note into a related table. I want to copy this note back into the main table and then delete the origincal record.
Table structues below;

Main Table (notes)
ID Serial note newnote Updater
1 1 12abc Text1 NATHAN
1 2 13abc Text3 NATHAN

Linked Note table (link)
ID Key Text
1 12abc Text1
1 13abc Text2

I need to move the text column from the linked note table into the newnote column in the main table when the note is inserted into the note column + linked note table.

I have created the below trigger, however it always inserts the proviuos note that was inserted;

CREATE TRIGGER CopyNotes on dbo.Notes FOR insert
AS
Declare @key varchar (13),
@updater varchar (20),
@serial int

SELECT @updater=updater,@key=note,@serial=serial from INSERTED

IF @updater='NATHAN'
UPDATE dbo.notes
SET newnote = s.text
FROM notes t, link s
WHERE t.serial = @serial

 

[nathanharcup]

After running an SQL trace I Can see that the triiger is running before the data is entered into the related table. Not sure how I could delay the trigger from running untill the related table has recived the data

 

[nathanharcup]

Got there, just created the trigger on the second table to be inserted to


hard coding an application may seem like a nightmare but you can always get around it!