CODEWITHSUNDEEP
sqloracle-databaseplsql
raltandi
raltandi

Reputation: 97

PL/SQL Before Trigger - Inherit column from other table

I'm very very very new to PL/SQL so excuse my ignorance on this subject, but I'd appreciate your help!

I have these 2 tables, let's say, RATINGS and GAMES.

Suppose that I want to inherit a value from the RATINGS table and insert it into the GAME table automatically depending on the GAMERATING (inheriting it from the RATINGS TABLE) I gave it.

On the GAME table we have: (GAMEID, GAMEPRICE, GAMENAME, GAMERATING, GAMEAGES, PUBLISHERID)

And on the RATINGS table we have: (RATINGID, RATINGCODE, RATINGAGES)

If I insert the following values into GAME: (1,50,'Lost','M') I want it to automatically insert the GAMEAGES from the RATINGS table using the GAMERATING as a basis for reading from the RATINGS table, specifically from RATINGCODE.

How would I go about on doing this?

All I know is I have to declare variables and insert the ages into them depending on the GAMERATING.

EDIT:

Managed to do it guys,

here's the format I used:

CREATE OR REPLACE TRIGGER [triggername]

BEFORE INSERT ON [tablename]

FOR EACH ROW

DECLARE

[variables]

BEGIN

SELECT [columns to populate variables] INTO [variables] FROM [second table] JOIN [first table] ON [secondtableid] = :New.[firsttableid] GROUP BY [columns to pop variables] ORDER BY MAX([firsttableid])

:New.[firsttablecolumn] := [variables]

END

Hope this helps someone in the future

Upvotes: 0

Views: 171

Answers (1)

raltandi
raltandi

Reputation: 97

CREATE OR REPLACE TRIGGER [triggername]
BEFORE INSERT ON [tablename]
FOR EACH ROW
DECLARE
[variables]
BEGIN
SELECT [columns to populate variables] INTO [variables] FROM [second table] JOIN [first table] ON [secondtableid] = :New.[firsttableid] GROUP BY [columns to pop variables] ORDER BY MAX([firsttableid])
:New.[firsttablecolumn] := [variables]
END

Upvotes: 1

Related Questions