Trigger

Question

Post reply

Trigger

Munabhai

Default port

Points: 1445
More actions
March 15, 2012 at 10:31 am

#253063

create table product1
(
productid int,
productname varchar(20),
costprice int,
sellingprice int
)
My trigger should not allow to insert such a DATA whose costprice is greater then sellingprice.
create trigger abcd1
on product1 for insert
as
declare @cp int, @sp-2 int
select @cp = costprice, @sp-2 = sellingprice from inserted
if @cp > @sp-2
rollback tran
-following insert command, data donot insert but show an error
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
insert into product1 values('3','cup', 34,21)
--following insert command data will insert but still show an error
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
insert into product1 values('4','mouse', 50,51)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Why not use a table constraint where SellingPrice > CostPrice?

Lowell SSC Guru Points: 323518 More actions · Answer 2

first, you don't need a trigger...you could simply add a check constraint to prevent that.

ALTER TABLE product1 ADD CONSTRIANT CK_COST_VS_SELL CHECK(costprice <= sellingprice)

your trigger assumes only one row being inserted, instead, it should check all rows in the insert, and reject the insert if even one mistake exists.

create trigger abcd1

on product1 for insert

as

if EXISTS(SELECT 1 FROM inserted WHERE costprice > sellingprice)

rollback tran

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Munabhai Default port Points: 1445 More actions · Answer 3

Munabhai

Default port

Points: 1445

March 15, 2012 at 10:42 am

#1460000

Thanks, it work with constraint.