shohelr2003 (3/20/2013)
CREATE TRIGGER [dbo].[Discount]
ON [dbo].[Product]
INSTEAD OF INSERT
AS
Declare @disc int;
Declare @line int;
Declare @pdid as varchar(10);
BEGIN
select @disc = i.DiscPercentage from inserted i
select @line = i.LineNumber from inserted i
select @pdid = i.ProdId from inserted i
IF (@disc>18 and @pdid between 1000 and 2000)
RAISERROR(N'Please Type Correct Discount',16,1);
ROLLBACK;
END
You shouldn't write trigger as above. This is very insecure way to write it.
inserted and deleted internal tables can contain multiple rows, therefore you cannot read from them into variables. Also, in your instance , you need just simple FOR INSERT trigger - not INSTEAD OF one:
CREATE TRIGGER [dbo].[tr_Product_Discount]
ON [dbo].[Product]
FOR UPDATE
AS
BEGIN
IF EXISTS (SELECT 1 FROM inserted
WHERE DiscPercentage > 18
AND ProdId between 1000 and 2000)
BEGIN
RAISERROR(N'Please Type Correct Discount',16,1);
ROLLBACK;
END
END
Saying the above, the trigger is really overkill design here.
The better design would be creating table check constraint and validation in UI.