468,238 Members | 1,683 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,238 developers. It's quick & easy.

Trigger Problem

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
2 771
Marjeta
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
2,878 Expert 2GB
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.

Similar topics

2 posts views Thread by Galina | last post: by
9 posts views Thread by Martin | last post: by
13 posts views Thread by Tolik Gusin | last post: by
5 posts views Thread by William of Ockham | last post: by
2 posts views Thread by gustavo_randich | last post: by
12 posts views Thread by Bob Stearns | last post: by
2 posts views Thread by mob1012 via DBMonster.com | last post: by
2 posts views Thread by dean.cochrane | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by kermitthefrogpy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.