Solved

Problem with trigger

Posted on 2011-02-17
1
829 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

770 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