| Trigger to work in a single record [message #274586] |
Tue, 16 October 2007 08:52  |
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 #274595 is a reply to message #274588] |
Tue, 16 October 2007 09:26  |
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 #274617 is a reply to message #274610] |
Tue, 16 October 2007 10:51 |
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 |
Frank
Messages: 7901
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 |
 |
Michel Cadot
Messages: 68641
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
