0

Given a table ITEM as below :

CREATE TABLE ITEM (
ItemNo        int not null,
category      varchar(20) not null,
Quantity      int not null,
Price       decimal(10,2) not null,
Total       decimal(10,2) not null,
CONSTRAINT pk_item primary key (ItemNo)
);

I need to write a row-level trigger called update_item that will fire whenever Quantity and/or Price is inserted/updated. When this happens, the Total column will be set by the trigger as the product of Quantity and Price.

Any help will be appreciated.

2
  • Does it have to be a trigger?...a generated column would be a lot simpler and likely a more performant approach to automatically calculate the Total here. If such an alternative is acceptable I'll post an answer. – J.D. Apr 11 at 12:55
  • You shouldn't store any data that can be computed at query runtime. – mustaccio Apr 11 at 14:29
1

In MySQL you need to make deferent trigger for updating and Inserting;

DELIMITER $$

CREATE TRIGGER before_ITEM _insert
BEFORE INSERT
ON ITEM FOR EACH ROW
BEGIN
   SET NEW.Total = NEW.Quantity * NEW.Price;   
END $$

DELIMITER ;

CREATE TRIGGER before_ITEM _UPDATE
BEFORE UPDATE
ON ITEM FOR EACH ROW
BEGIN
   SET NEW.Total = NEW.Quantity * NEW.Price;   
END $$

DELIMITER ;

But for that purpose you can use generated Columns like

CREATE TABLE ITEM (
ItemNo        int not null,
category      varchar(20) not null,
Quantity      int not null,
Price       decimal(10,2) not null,
Total       decimal(10,2) GENERATED ALWAYS AS (Quantity * Price),
CONSTRAINT pk_item primary key (ItemNo)
);
4
  • Why on BEFORE INSERT ? I was thinking it will be AFTER UPDATE OR INSERT operation ? Isn't it ? – Hacker688 Apr 11 at 11:12
  • It must be BEFORE to uupdate the value of the total column, that is how tirggers work. AFTER INSERT is only for updateing other tables, else it fails – nbk Apr 11 at 11:16
  • One can't write a single trigger to handle INSERT OR UPDATE operations ? Something like CREATE TRIGGER update_item BEFORE UPDATE OR INSERT ON ITEM – Hacker688 Apr 11 at 11:24
  • no mysql has no option for that. an isert has only NEW and Update HAs NEWand OLD and a DELTE Triger has only OLD, to handle old and new values, so chekc the documentation – nbk Apr 11 at 12:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.