sql trigger using two tables data

Question

So I have three tables. First two have ID column and "rank" column (an integer). I will be inserting into the third table both IDs (ID1, ID2) but I need the trigger to check whether they are the same rank before I can insert. I can't get it to work.

CREATE OR REPLACE TRIGGER TRIGGER1 
AFTER INSERT ON TABLE_C 

BEGIN
IF NOT EXISTS (
SELECT TABLE_A.id, TABLE_B.id
FROM TABLE_A JOIN TABLE_B ON TABLE_A.rank = TABLE_B.rank
WHERE TABLE_A.id = inserted.id1 AND TABLE_B.id = inserted.id2 )
THEN
PRINT 'Not the same rank'
ROLLBACK
END
END;

I'm using Oracle db.

The syntax is so wrong ... have you looked at any Oracle trigger documentation? — David Aldridge
– David Aldridge, Commented May 12, 2013 at 11:44

pratik garg · Accepted Answer · 2013-05-12 10:56:08Z

1

you can not use 'Rollback' or 'commit' in Oracle Triggers - please refer this

I think you can modify your trigger as follow -

CREATE OR REPLACE TRIGGER Trigger1
    AFTER INSERT ON Table_c
    FOR EACH ROW
  DECLARE
    l_Count NUMBER := 0;
  BEGIN
    SELECT COUNT(*)
      INTO l_Count
      FROM Table_a
      JOIN Table_b ON Table_a.Rank = Table_b.Rank
     WHERE Table_a.Id = :NEW.Id1
       AND Table_b.Id = :NEW.Id2;
    IF l_Count = 0 THEN
      DELETE FROM Table_c
       WHERE Id1 = :NEW.Id1
         AND Id2 = :NEW.Id2;
      --PRINT 'Not the same rank'
      Dbms_Output.Put_Line('Not the same rank');
    END IF;
  END;
/

answered May 12, 2013 at 10:56

pratik garg

3,3421 gold badge19 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Semih Yagcioglu · Accepted Answer · 2013-05-12 10:54:16Z

1

Triggers are two kinds, BEFORE and AFTER Triggers. If you want to check whether the data has the same rank before the data is inserted, then you should use a BEFORE Trigger.

CREATE OR REPLACE TRIGGER TRIGGER1 
BEFORE INSERT ON TABLE_C

Then the logic will follow.

UPDATE: You can easily raise application error. Such as:

IF HasSameRank == 0 THEN
    raise_application_error(-21013, 'Not the same rank');
END IF;

edited May 12, 2013 at 10:54

answered May 12, 2013 at 10:36

Semih Yagcioglu

4,1111 gold badge29 silver badges43 bronze badges

3 Comments

user1418018 Over a year ago

Yeah, sorry I did try both but there's still something wrong.

pratik garg Over a year ago

@semihyagcioglu please suggest how to fail insert in trigger with "before insert" condition..

user1418018 Over a year ago

It says "Error(5,18): PLS-00103: Encountered the symbol "TABLE_B" when expecting one of the following: ) , with group having intersect minus start union where connect " It says the error is where the JOIN is, on TABLE_B

Collectives™ on Stack Overflow

sql trigger using two tables data

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related