CODEWITHSUNDEEP
mysqlsqldatabasetriggersinsert
PeakGen
PeakGen

Reputation: 22995

Creating an "After Insert" Trigger

Please have a look at the below diagram

enter image description here

I need to write a Trigger in Ongoing_Portfolio where it automatically updates Ongoing_Fees after Insert operation.

The Portfolio.Other_Fee is a percentage, while Ongoing_Fees.Other_Fee = Ongoing_Portfolio.Cash_Value * Portfolio.Other_Fee.

Please note the idPortfolio in all 3 tables. What happens here is after user Inserted data into Ongoing_Portfolio, it scans for the related Portfolio using idPortfolio. Then it grabs the Other_Fee from the related portfolio and insert the data into Ongoing_Fees.

Below is the Trigger I wrote, but I couldn't write the insert method to do the Other_Fee.

CREATE TRIGGER `Ongoing_Portfolio_AINS` AFTER INSERT ON `Ongoing_Portfolio` FOR EACH ROW
INSERT INTO Ongoing_Fees (idPortfolio,Other_Fee)
VALUES
(New.idPortfolio, )

How can I complete this Trigger?

example:

Portfolio.idPortfolio = 1
Portfolio.Other_Fee = 10%

Now I insert data into Ongoing_Portfolio

Ongoing_Portfolio.idPortfolio = 1
Ongoing_Portfolio.Cash_Value = 1000

Now, the trigger has triggered, so the data in Ongoing_Fees should be

Ongoing_Fees.idPortfolio = 1
Ongoing_Fees.Other_Fee = 100

Upvotes: 0

Views: 78

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Is this what you need?

INSERT INTO Ongoing_Fees (idPortfolio,Other_Fee)
    select New.idPortfolio, p.other_fee
    from Portfolio p
    where p.idPortfolio = New.idPortfolio;

Upvotes: 3

Related Questions