December 28, 2012 at 9:47 am
I have the following to create a trigger. But get this error message:
Msg 207, Level 16, State 1, Procedure triggername, Line 7
Invalid column name 'field1'.
Msg 207, Level 16, State 1, Procedure triggername, Line 7
Invalid column name 'field2'.
One other question..why do I have to include the 16,1 after my text for the error in the raiserror statement ( RAISERROR ('Text of error',16,1)?
SQL statement
CREATE TRIGGER triggername
ON tablename
AFTER INSERT, UPDATE
AS
BEGIN
IF
([field1]=1 and [field2] Is Null)
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('Text of error',16,1)
End
END
GO
December 28, 2012 at 9:57 am
your trigger needs to refer to a table, just like any other SELECT statement does;
when inside a trigger, you need to use the virtual tables INSERTED or DELETED, depending on the operation you are testing.
something like this is what you are after, i think:
CREATE TRIGGER triggername
ON tablename
AFTER INSERT, UPDATE
AS
BEGIN
IF EXISTS(SELECT 1
FROM INSERTED
WHERE ( [field1] = 1 --this column must actually exist in the table
AND [field2] IS NULL )) --this column must actually exist in the table
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('Text of error: Field2 cannot be null when Field1 has a value of one.',16,1)
END
END
GO
For the Why 16 question,
any error greater than 16 is "Severe" enough to rollback a calling transaction as well.
Lowell
December 28, 2012 at 10:07 am
Thanks..it worked
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply