Friday, March 23, 2012

How to reference the primary key of a newly added record in trigger?

Please help me somebody solve my problem with my first :o trigger:

ALTER TRIGGER partner_update
ON dbo.partner
FOR UPDATE
AS
INSERT INTO partner (name) SELECT name FROM deleted
UPDATE invoice SET id_partner= *** WHERE id_partner = (SELECT id_partner FROM deleted)


*** - here I want to add a "reference" to the newly added record's automatically generated primary key (not to the updated!) Is it possible?I'd use:ALTER TRIGGER partner_update
ON dbo.partner
FOR UPDATE
AS
BEGIN
INSERT INTO partner (name)
SELECT name FROM deleted

UPDATE o
SET id_partner= i.id_partner
FROM dbo.invoice AS o
INNER JOIN deleted AS d
ON (d.id_partner = o.id_partner)
LEFT OUTER JOIN inserted AS i
ON (i.id_partner = o.id_partner)
ENDThis trigger takes a rather perverse view of the universe, allowing for the possiblity that some low-down, high-smelling, bundle-of-dung might possibly change the value of the id_partner column. The invoice table might or might not like that, since the trigger would then NULL out the invoice.id_partner column!

-PatP

No comments:

Post a Comment