By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,892 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

Trigger Problem

P: 90
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
Expand|Select|Wrap|Line Numbers
  1. create trigger ins_trigger on table2 
  2. after insert as
  3. declare @id int
  4. update table1 set quantity=quantity-1 where id=@id
  5.  
the query got executed by the table1 is not updated by -1.
what is the problem that is encountered here.

thanks in advance.

Raghul
Sep 23 '08 #1
Share this Question
Share on Google+
2 Replies


P: 25
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.
Sep 23 '08 #2

ck9663
Expert 2.5K+
P: 2,878
It's because @id is NULL. Get the value of the id column from inserted table. Something like:
Expand|Select|Wrap|Line Numbers
  1. create trigger ins_trigger on table2 
  2. after insert as
  3. declare @id int
  4. update table1 set quantity=quantity-1 
  5. where id in (select id from inserted)
-- CK
Sep 23 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.