trigger help with syntax

[tester321]

Thanks. I'm trying my first table trigger for sql2005, can't seem to get it right? I'm getting incorrect syntax near keyword "then" line 10, line 12 incorrect syntax near SurveyID, and line 33 db2_RDT.dbo.SurveyMember.SurveyID is not permitted in this context...column names are not permitted.

I have 2 db in the same server, db2_rdt needs to update the same table with the same columns/types as db1

This is what i have written, hope it post correctly.. Thanks

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER updateSurveyMember
ON [db2_RDT].[dbo].[SurveyMember]
AFTER INSERT,DELETE,UPDATE
AS
BEGIN

SET NOCOUNT ON;

if UPDATE then
UPDATE [db1].[dbo].[SurveyMember]
SET [db1].[dbo].[SurveyMember]SurveyID = [db2_RDT].[dbo].[SurveyMember].SurveyID
, [db1].[dbo].[SurveyMember]MemberID = [db2_RDT].[dbo].[SurveyMember].MemberID
, [db1].[dbo].[SurveyMember]AcceptedTOUOn = [db2_RDT].[dbo].[SurveyMember].AcceptedTOUOn
, [db1].[dbo].[SurveyMember]Started = [db2_RDT].[dbo].[SurveyMember].Started
, [db1].[dbo].[SurveyMember]StartedOn = [db2_RDT].[dbo].[SurveyMember].StartedOn
, [db1].[dbo].[SurveyMember]Completed = [db2_RDT].[dbo].[SurveyMember].Completed
, [db1].[dbo].[SurveyMember]Referred = [db2_RDT].[dbo].[SurveyMember].Referred
, [db1].[dbo].[SurveyMember]BroadcastID = [db2_RDT].[dbo].[SurveyMember].BroadcastID
end if

if INSERT then
INSERT INTO [db1].[dbo].[SurveyMember]
(SurveyID
, MemberID
, AcceptedTOUOn
, Started
, StartedOn
, Completed
, Referred
, BroadcastID)
VALUES( [db2_RDT].[dbo].[SurveyMember].SurveyID
, [db2_RDT].[dbo].[SurveyMember].MemberID
, [db2_RDT].[dbo].[SurveyMember].AcceptedTOUOn
, [db2_RDT].[dbo].[SurveyMember].Started
, [db2_RDT].[dbo].[SurveyMember].StartedOn
, [db2_RDT].[dbo].[SurveyMember].Completed
, [db2_RDT].[dbo].[SurveyMember].Referred
, [db2_RDT].[dbo].[SurveyMember].BroadcastID);
end if

if DELETE then
DELETE FROM [db1].[dbo].[SurveyMember]
WHERE [db1].[dbo].[SurveyMember].SurveyID = [db2_RDT].[dbo].[SurveyMember].SurveyID, [db1].[dbo].[SurveyMember].MemberID = [db2_RDT].[dbo].[SurveyMember].MemberID, [db1].[dbo].[SurveyMember].BroadcastID = [db2_RDT].[dbo].[SurveyMember].BroadcastID
end if

END
GO

 

[rac2]

Thats easy. SQL Server does not use IF...THEN

IF Boolean_expression
{ sql_statement | statement_block }
[ ELSE
{ sql_statement | statement_block } ]

E.g.

IF UPDATE
BEGIN
  UPDATE [db1].[dbo].[SurveyMember] SET
         [db1].[dbo].[SurveyMember]MemberID = inserted.MemberID 
[COLOR=red]
  WHERE [db1].[dbo].[SurveyMember]SurveyID = inserted.SurveyID
[/color]
END

And dont forget that UPDATE queries in a trigger are just like all update queries, they need a WHERE clause to specify which row to update. Otherwise every row will be changed.

The special thing about a trigger is that it has two temporary tables named deleted and inserted which contain the before and after values from the row which is changed in the table which has the trigger.

BTW, you are a step ahead of me. I have never written a trigger, but I read about it in a book. The IF ... ELSE part I know from experience.

HTH

 

[tester321]

Thanks rac2, i'll try that. cheers

 

[SQLSister]

And make sure that the trigger will work if multiple rows are inserted, updated or deleted. otherwise you will have data integrity problems. You cannot use the values clause for instance if you want to do this. Use a select statement inthe insert instead. You will need to join to these tables inthe update as well. The delete might be better taken care of in a cascading delete.

Questions about posting. See faq183-874