Home » Other » Training & Certification » Trigger to work in a single record
Trigger to work in a single record [message #274586] Tue, 16 October 2007 08:52 Go to next message
drimades
Messages: 13
Registered: June 2007
Junior Member
I have a table Penalties with a list of penalties to pay for each player. In another table Players I have the list of all the players with an ID for each player. I need to create a trigger that updates the sum of penalties for the player concerned (table "Players") for every new entry in the table Penalties. Any idea?
Re: Trigger to work in a single record [message #274587 is a reply to message #274586] Tue, 16 October 2007 08:55 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Why would you need this stored in the database?
Couldn't you do the sum with whatever query you are using for display/reporting ?
Re: Trigger to work in a single record [message #274588 is a reply to message #274586] Tue, 16 October 2007 09:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Never do that it will failed in multi-user environment.

Regards
Michel
Re: Trigger to work in a single record [message #274595 is a reply to message #274588] Tue, 16 October 2007 09:26 Go to previous messageGo to next message
drimades
Messages: 13
Registered: June 2007
Junior Member
It is for an exam.

Does this work?

CREATE TRIGGER update_tot

AFTER INSERT ON Penalties

UPDATE Players
SET total_penalties = :old.total_penalties + :new.penalty_value


The problem is that :old and :new refer to different tables ... ??
Re: Trigger to work in a single record [message #274610 is a reply to message #274595] Tue, 16 October 2007 10:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No this will not work.
:new and :old refer to the table you update.

Regards
Michel
Re: Trigger to work in a single record [message #274617 is a reply to message #274610] Tue, 16 October 2007 10:51 Go to previous messageGo to next message
drimades
Messages: 13
Registered: June 2007
Junior Member
What about this?

CREATE OR REPLACE TRIGGER penalties_AIR
AFTER INSERT
ON penalties
REFERENCING NEW AS NEW
FOR EACH ROW
begin
update players
set total_penalties = total_penalties + :NEW.penalty_value
where player_id = :NEW.player_id;
end;

Is it mandatory to tell "FOR EACH ROW" if I know that I have to update only one row of the player concerned?
Re: Trigger to work in a single record [message #274621 is a reply to message #274617] Tue, 16 October 2007 10:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
if it's for an exam, you can earn extra points by issueing the code as requested (otherwise they will reject your answer). Then go search why this is a bad idea, and add that to your answer.
If your teacher is a savvy, he should appreciate it.
Re: Trigger to work in a single record [message #274626 is a reply to message #274617] Tue, 16 October 2007 11:12 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now it depends which table you update and what the table descriptions.

And do as Frank said, search why it is a bad idea that will never work in real world.

Regards
Michel
Previous Topic: Can you please tell us OCA certification centers in Bangalore?
Next Topic: SQL-aggregate function
Goto Forum:
  


Current Time: Fri Dec 09 04:13:46 CST 2016

Total time taken to generate the page: 0.13784 seconds