SQL Trigger

[nmarun]

Hi Gurus,

Table PassengerInfo

PID as varchar(6),
FName as varchar(15),
LName as varchar(15),
Points as int,
Primary Key (PID)

Table Sweepstakes

PID as varchar(6),
FName as varchar(15),
LName as varchar(15),
Primary Key (PID)

Following is what should happen. Here James Bond just updated his points to 102 from 95. The trigger needs to add a row of data for James Bond in table Sweepstakes and also set the value of his points in passengerInfo as 0.

PassengerInfo
PASS12, James, Bond, 102

Sweepstakes
PASS12, James, Bond

PassengerInfo
PASS12, James, Bond, 0


CREATE TRIGGER trPoints100 ON dbo.PassengerInfo
AFTER UPDATE
AS
SET NOCOUNT ON

if Points > 99

begin
UPDATE dbo.PassengerInfo SET Points = 0 where PID = inserted.PID
INSERT into dbo.Sweepstakes (PID, FName, LName) Select PID, FName, LName from inserted
end

FROM
PassengerInfo pi, inserted i
WHERE
i.PID = pi.PID

The above code is what I tried but it gives an error saying "Incorrect syntax near keyword 'FROM'" :confused:

Can you also provide me with websites where I can read and learn about triggers?

[nmarun]

Dear Gurus

I got the solution for the question..


CREATE TRIGGER trPoints100 ON dbo.PassengerInfo
AFTER UPDATE
AS
SET NOCOUNT ON
IF UPDATE(Points)
UPDATE PassengerInfo SET Points = 0
INSERT into Sweepstakes(PID, FName, LName) SELECT i.PID, i.FName, i.LName
FROM PassengerInfo pi, inserted i
WHERE i.PID = pi.PID and i.Points > 99


Did someone say - "perseverance pays"??