Trigger Problem

[raghulvarma]

I have two tables
table1 has three columns (id int ,book varchar(20),quantity int)
table2 has two columns (id int,book varchar(20))
now if I insert a row in table2 then the quantity of the particular id has to be reduced by 1.
I tried my query as

create trigger ins_trigger on table2 
after insert as
declare @id int
update table1 set quantity=quantity-1 where id=@id
 

the query got executed by the table1 is not updated by -1.
what is the problem that is encountered here.

thanks in advance.

Raghul

[Marjeta]

I have two tables
table1 has three columns (id int ,book varchar(20),quantity int)
table2 has two columns (id int,book varchar(20))
now if I insert a row in table2 then the quantity of the particular id has to be reduced by 1.

I'm trying to understand what you do here...

would the first table be something like:
1 bookA 13
2 bookB 24
3 bookC 2
with first column being the primary key and thus unique?

And then you would insert lines in second table, like:
1 bookA
3 bookC
1 bookA
2 bookB
2 bookB
where neither column is unique.

And you would want this to make the following changes to first table:
1 bookA 11
2 bookB 22
3 bookC 1

Is that what you are doing? In that case, why do you need 'book' column in both tables, and 'id' uniquely defines the book? You could just keep id in the second table.

[ck9663]

It's because @id is NULL. Get the value of the id column from inserted table. Something like:

create trigger ins_trigger on table2 
after insert as
declare @id int
update table1 set quantity=quantity-1 
where id in (select id from inserted)

-- CK