1

I have a table of bids and everytime a bid is added in an auction I want to create new notifications to every bidder in that auction. As this is for a class of databases I though of doing a trigger to show some skills in oracle pl/sql. My trigger is like this:

CREATE OR REPLACE TRIGGER create_notifs
AFTER INSERT ON bid
FOR EACH ROW
BEGIN
  INSERT INTO notification(id_notif, id_auction, username, time, seen_state)
  SELECT notif_id_seq.NEXTVAL, :NEW.id_auction, username, SYSDATE, 0
  FROM bid
  WHERE bid.id_auction = :NEW.id_leilao;
END;
/

Now this trigger won't work because it's accessing the table that fired it. It is not a problem though because I'm not changing it and it really needs to be after the insertion because I need to see if it passes the constraints. How can I do this?

Joaquim Ferrer
  • 603
  • 6
  • 23

1 Answers1

0

You can't do a SELECT from the table on which a row trigger is declared or you get the dreaded "mutating table" error, but it doesn't look to me like you need to SELECT anything. Try rewriting your trigger as:

CREATE OR REPLACE TRIGGER create_notifs
  AFTER INSERT ON bid
  FOR EACH ROW
BEGIN
  INSERT INTO notification
    (id_notif, id_auction, username, time, seen_state)
  VALUES
    (notif_id_seq.NEXTVAL, :NEW.id_auction, username, SYSDATE, 0);
END;

Best of luck.

Bob Jarvis - Слава Україні
  • 48,992
  • 9
  • 77
  • 110
  • But I need the usernames that are associated to the auction through the bid table. – Joaquim Ferrer Dec 15 '16 at 03:43
  • Then the right thing to do is to find the username in your program logic and put the correct username in when doing the INSERT. This smacks of putting [business logic in a trigger](http://stackoverflow.com/questions/31998789/trigger-to-enforce-m-m-relationship), which is a bad idea anyways. – Bob Jarvis - Слава Україні Dec 15 '16 at 03:51
  • @JoaquimFerrer Use **USER** to get user name of connected session – hmmftg Dec 15 '16 at 11:15