June 22, 2009 at 10:10 am
The record I am inserting does not exist in the table. I think it is something with the syntax or something I am missing...please help
ALTER TRIGGER [dbo].[trg_insupd_New] ON [dbo].[New]
FOR insert, update
AS
DECLARE @IsPublic bit, @IsFilter bit, @UsersId uniqueidentifier, @QueryName as NVarchar(50)
SELECT @IsPublic = IsPublic, @IsFilter =IsFilter, @UsersId = ISNULL([UsersId],'00000000-0000-0000-0000-000000000000'), @QueryName = QueryName FROM INSERTED
IF EXISTS (SELECT 1FROM New
WHERE QueryName = @QueryName
AND IsFilter = @IsFilter
AND IsPublic = @IsPublic
AND ISNULL(UsersId,'00000000-0000-0000-0000-000000000000') = @UsersId)
BEGIN
RAISERROR('ERROR_2: A Public Query Name needs to be unique in the DB.',16,1)
ROLLBACK
END
When I run this outside the trigger it acts like i would expect. It prints Test.
IF EXISTS(SELECT 1
FROM NewQueries NQ
WHERE NQ.QueryName = 'Jason Rocks'
AND NQ.[IsFilter] = 0
AND NQ.[IsPublic] = 1
AND ISNULL(NQ.[UsersId],'00000000-0000-0000-0000-000000000000') = '00000000-0000-0000-0000-000000000000')
BEGIN
RAISERROR('ERROR_2: A Public Query Name needs to be unique in the DB.',16,1)
ROLLBACK
END
ELSE
Print 'Test'
June 22, 2009 at 10:58 am
I am thinking that when it query the record already exists so it errors. Is there a way to check the record before it exists?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply