CODEWITHSUNDEEP
mysqlsql
jakeldots
jakeldots

Reputation: 23

how to use after insertion triggers in sql

+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| _date  | varchar(20) | NO   | PRI | NULL    |       |
| petrol | int(11)     | NO   |     | NULL    |       |
| diesel | int(11)     | NO   |     | NULL    |       |
| gas    | int(11)     | NO   |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| _date1  | varchar(20) | NO   | PRI | NULL    |       |
| petrol1 | int(11)     | NO   |     | NULL    |       |
| diesel1 | int(11)     | NO   |     | NULL    |       |
| gas1    | int(11)     | NO   |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

I am new at triggers and SQL overall and wanted to use after insert trigger such that once i enter values in the table "buy" mentioned above. The same value inserted must be inserted into the "Sell" table(also mentioned above) but after a mathematical operation : 

petrol+petrol*0.3, diesel+diesel*0.15, gas+gas*0.25

I tried to use this:

CREATE TRIGGER t1
AFTER INSERT
ON buy
FOR EACH ROW
BEGIN
(
    INSERT INTO Sell (_date1, petrol1, diesel1, gas1)
    SELECT _date, petrol+petrol*0.3, diesel+diesel*0.15, gas+gas*0.25
    FROM inserted
)
END

But then, it showed this error below:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'after insert on buy for each row begin (insert into Sell (_date1, petrol1, diese' at line 1

Upvotes: 2

Views: 64

Answers (2)

DarbyM
DarbyM

Reputation: 1203

Your Referenced MySQL 8 Document page

Read down you will find the following two statements:

Within the trigger body, the OLD and NEW keywords enable you to access columns in the rows affected by a trigger. OLD and NEW are MySQL extensions to triggers; they are not case-sensitive.

In an INSERT trigger, only NEW.col_name can be used; there is no old row. In a DELETE trigger, only OLD.col_name can be used; there is no new row. In an UPDATE trigger, you can use OLD.col_name to refer to the columns of a row before it is updated and NEW.col_name to refer to the columns of the row after it is updated.

So with that I entirely agree with @Gordon Linoff

What you're looking for is:

DELIMITER $$
CREATE TRIGGER t1 AFTER INSERT ON buy FOR EACH ROW
BEGIN
    INSERT INTO Sell (_date1, petrol1, diesel1, gas1)
    VALUES (NEW._date, NEW.petrol * 1.3, NEW.diesel * 1.15, NEW.gas * 1.25);
END $$
DELIMITER ;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You seem to want:

delimiter $$
create trigger t1 after insert on buy for each row
begin
    insert into Sell (_date1, petrol1, diesel1, gas1)
        values (new._date, new.petrol * 1.3, new.diesel * 1.15, new.gas * 1.25)
end;$$

delimiter ;

Upvotes: 1

Related Questions