Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Triggers Expand / Collapse
Author
Message
Posted Thursday, May 23, 2013 11:35 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 2:22 PM
Points: 55, Visits: 165
SQL2000

Need help with triggers. I'm trying to create a trigger that checks the table; if the record exists based on a few criterias, then it won't insert, otherwise insert it.

This is my attempt following http://www.sqlservercentral.com/articles/Triggers/64214


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'BI_XYZ_TRI' AND type = 'TR')
DROP TRIGGER BI_XYZ_TRI
GO

CREATE TRIGGER BI_XYZ_TRI
ON XYZ
INSTEAD OF INSERT
AS

BEGIN
SET NOCOUNT ON;
IF NOT EXISTS (SELECT * FROM inserted a JOIN xyz b
ON a.criteria1 = b.criteria1
AND a.criteria2 = b.criteria2
AND a.criteria3 = b.criteria1
AND a.criteria4 = b.criteria1
AND a.criteria5 = b.criteria5)
BEGIN
INSERT INTO dbo.xyz
( col1,
col2,
col3,
col4,
col5,
...
)
SELECT col1,
col2,
col3,
col4,
col5,
...
FROM inserted
END -- ends insert sttmt
ELSE
BEGIN
RAISERROR ('Duplicate Records')
END -- ends raiserror
END -- ends trigger

GO


I'm getting error
Incorrect syntax near ')'


Can anyone help? Thank you
Post #1456130
Posted Thursday, May 23, 2013 11:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:11 AM
Points: 12,881, Visits: 31,823
pretty sure RAISERROR requires three parameters, and you only passed one.
RAISERROR ('Duplicate Records',16,1)


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1456134
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse