trigger

[natx]

i have the following trigger, and although they dont give me any syntax error. They dont work. Could someone help me?

CREATE TRIGGER [regiontree] ON [member]
FOR INSERT, UPDATE
AS
SELECT i.userid = m.userid
FROM inserted i, member m
IF i.userid = m.userid
BEGIN
UPDATE [member]
SET region = Region.area
FROM Region
WHERE Region.country = member.country
END
---------------------------------------

CREATE TRIGGER [regiontree] ON [member]
FOR INSERT, UPDATE
AS
UPDATE [member]
SET member.region = Region.area
FROM
[member]
JOIN Region ON Region.country = member.country
JOIN inserted ON inserted.userid = member.userid

----------------------------------
This trigger gives an error in Line 4: error near '='

CREATE TRIGGER [regiontree] ON [member]
FOR INSERT, UPDATE
AS
SELECT i.userid = m.userid
FROM inserted i, member m
IF i.userid = m.userid
BEGIN
UPDATE [member]
SET region = Region.area
FROM Region
WHERE Region.country = member.country
END
------------------------------------
Thanks so much
natalia

http://www.nataliaz.com

 

[tlbroadbent]

Samples 1 and 3 are totally incorrect. Trigger 2 should work with a slight correction.

CREATE TRIGGER [regiontree] ON [member]
FOR INSERT, UPDATE
AS
SET NOCOUNT ON

UPDATE [member]
SET region = Region.area
FROM [member]
INNER JOIN Region ON Region.country = member.country
INNER JOIN inserted ON inserted.userid = member.userid

SET NOCOUNT OFF

If it doesn't work, try the following select query in Query Analyzer or another tool to verify if the JOIN of the member and region tables returns the desired result.

Select member.userid, member.country, region.area
FROM [member]
INNER JOIN Region
ON Region.country = member.country
WHERE member.userid=<the id you updated> Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.