Problem with Trigger? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem with Trigger?

Author

Topic

SQLFOX
Starting Member

45 Posts

Posted - 2011-03-24 : 07:37:10

I have created a trigger to help with validating data. However, when I insert data I get the error below. The datatype of the Item column is Varchar(10). Is it the trigger that is causing the problem?

Msg 245, Level 16, State 1, Procedure trig_data_validation, Line 10
Conversion failed when converting the nvarchar value 'Egg' to data type int.

--Create a trigger
CREATE TRIGGER trig_data_validation
ON Orders
AFTER INSERT
AS
BEGIN
DECLARE @Item nvarchar(50)
SELECT @Item = Item
FROM inserted
IF Char (@Item) != 'Apple, Orange, Pear'
BEGIN
Print'Item being inserted is not allowed'
ROLLBACK TRANSACTION
END
END

GO

--Insert Data--
Insert Into Orders (OrderID, CustomerID, EmployeeID, OrderDate, PurchaseOrderNo, Item, UnitPrice, Comment)
Values (16, 3, 103, '2011-05-22', 97, 'Egg', 3, 'No comment today')

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-03-24 : 10:14:25

What did you mean by IF Char (@Item) != 'Apple, Orange, Pear'?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-03-27 : 01:20:25

i think this whole thing can be implemented with CHECK CONSTRAINT

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2011-03-27 : 15:11:34

Just run this and you will understand where the problem is.

Select char('Test')

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources