Trigger on SELECT?

[anthonyjpv]

Hi!

I have a table for payments

what I want to accomplish is:

once a certain customer Id reached a certain amount by his accumulated payment,

it will trigger an insert into statement to my discount table to generate a code specifically for him.

i understand thats trigger is only possible for insert update and delete statements

but is there a anyway of doing this by means of different method?

im thinking of automating the SELECT SUM(amount) FROM payments WHERE id=cust_id

then do a trigger of insert into discount table if certain condition from the sum(amount) has been met.

thanks!

[smantscheff]

Maybe you might use the INSERT IGNORE statement.
Generate a unique and reproducible discount code for customers with a certain amount of payments. Create a stored function for it (here called myCodeGenerator). Make the code column a unique key (maybe in conjunction with the customer id). Then use something like

INSERT IGNORE INTO discount_codes (id_customer,code)
  SELECT id_customer, myCodeGenerator(id_customer)
  FROM payments
  GROUP BY id_customer
  WHERE SUM(payment) > 100000

[anthonyjpv]

I did not quite understand the ignore part

can you explain a bit further?

what I want to accomplish is,

every insert of payment into payments table,

it will check based on sum of payments by customer id

and then it will generate a code to discounts table

thanks!

[Biiim]

how about this sort of approach?

DELIMITER $$

CREATE
    TRIGGER `database`.`trigger1` AFTER INSERT
    ON `database`.`table`
    FOR EACH ROW BEGIN
	#check sum reached and do whatever when it is
	#UPDATE `table` SET `field` = 'value'
    END$$

DELIMITER;

[anthonyjpv]

Biiim, you nail it! Thanks! I justhave to find out the next thing I wanna do

that is, check to see from payments table if sum of a certain customer has reached $1000 USD and then

if true check discounts table if already used a discount code
then generata a discunt code only if that same custoer reached $2000 USD

if not create a discount code in discounts table

thanks!

[anthonyjpv]

NOT TESTED

$transacion_id = mysql_insert_id();
 
//gets the customer id FROM payments table after the last INSERT
$query = "SELECT customer_id FROM payments WHERE transaction_id='{$transaction_id}'";
$result = mysql_query($query);
$row = mysql_fetch_array($result);
$customer_id = $row['customer_id'];
 
//gets the customer id FROM discounts table after the last payment INSERT
$query = "SELECT customer_id 
FROM discounts 
WHERE customer_id='{$customer_id}'";
$result = mysql_query($query);
$count = mysql_num_rows($result);
 
$current_year = DATE("Y");
 
$start_day = 1;
$start_month = 1;
 
$end_day = 31;
$end_month = 12;
 
if($count == 0)
{
	//gets the total sum FROM payments table after the last INSERT
	$query = "SELECT SUM(amount) 
	FROM payments 
	WHERE customer_id='{$customer_id}'
	AND (date >= $start_day, $start_month, $current_year && date <= $end_day, $end_month, $current_year)";
	$result = mysql_query($query);
	$row = mysql_fetch_array($result);
	$total = $row[SUM(amount)];
 
	if($total >= 8000)
	{
                //SOME sort of discount code generation here
 
		$query = "INSERT INTO discounts 
		(discount_code, 
		customer_id)
		VALUES
		('{$discount_code}','{$customer_id}')";
                mysql_query($query);
	}
}

this is the php code that I wat to automate after inserting a payment to payments table

so basically, I want this to be converted into trigger

[Biiim]

what about this?

DELIMITER $$
 
CREATE
    TRIGGER `email_data`.`trigger1` AFTER INSERT
    ON `email_data`.`orders`
    FOR EACH ROW BEGIN
    	INSERT IGNORE INTO discounts (discount_code, customer_id)
    	VALUES ('latestcode',NEW.cust_id) 
    	WHERE (SELECT SUM(amount) FROM payments WHERE customer_id = new.customer_id AND SUBSTR(`date`,1,4) = SUBSTR(CURDATE(),1,4)) >= 8000
    END$$
DELIMITER;

make sure theres a unique index on discount_code & customer_id or it will insert the same discount code for every order over 8000

[anthonyjpv]

I figured it ou by checking the total of payment sent by a certain customer after every payment and not using trigger. thanks anyway!