Solved

Problem with trigger

Posted on 2011-02-17
1
831 Views
Last Modified: 2012-08-14
I am using mysql 5.1

I have two tables that I am working with. I have a product table and a discount table.

When a product gets added, I use an after insert trigger to create a record in the discount table. Here is the trigger. I am using navicat to create the trigger and this is how it displays. It is firing After>Insert

insert into discount (product_id,amount,is_percent) values (New.product_id,New.product_price-New.discount_price,0);

This trigger works great. What I have to do after this is update the product table with a value from the newly added record in the discount table. Here is that trigger. This fires After>Insert and is where I receive an error. This trigger is in my discount table.

update product SET product_discount_id = NEW.discount_id where product_id = NEW.product_id

Should this work?


0
Comment
Question by:J C
1 Comment
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 34922629
Firstly, remove ambiguity on the product_id column, then please post the error message you are getting.

i.e., use this code:
UPDATE product 
SET product.product_discount_id = NEW.discount_id
WHERE product.product_id = NEW.product_id
;

Open in new window

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question