Trigger not ending execution or input

Question

I have to check before inserting or updating a certain relation whether a particular row attribute in relation A is equal or greater than a corresponding row attribute in relation B. I wrote a trigger:

Attempt:

CREATE TRIGGER CandyCons AFTER INSERT, UPDATE ON Rel_A
AS
IF EXISTS (
          SELECT * FROM Rel_A A, Rel_B B WHERE A.ID = B.ID AND A.candy > B.too_much
          )
BEGIN
    RAISERROR ('Stop eating!', 16, 1);
    ROLLBACK TRANSACTION;
    RETURN; 
END;

I can't execute this. When I run it, I start getting line numbers like:

query
12
13
14
15
......... and so on

What should I do?

RAISERROR is only available in SQL Server, if I recall correctly. ??? — Bob Jarvis - Слава Україні, Mar 28 '17 at 02:17

score 1 · Answer 1 · answered Mar 27 '17 at 23:35

This can be done with a row level TRIGGER as in the following example.

First create the tables:

CREATE TABLE REL_B(ID NUMBER NOT NULL PRIMARY KEY, TOO_MUCH NUMBER);
CREATE TABLE REL_A(ID NUMBER NOT NULL REFERENCES REL_B(ID), CANDY NUMBER);

Then add some test data:

INSERT INTO REL_B VALUES (1,10);
INSERT INTO REL_B VALUES (2,20);
COMMIT;

Then create your TRIGGER:

CREATE OR REPLACE TRIGGER CANDYCONS
  AFTER INSERT OR UPDATE ON REL_A
  FOR EACH ROW
  DECLARE
    V_COUNT_TOO_MUCH_CANDY NUMBER := 0;
    BEGIN
    SELECT COUNT(*) INTO V_COUNT_TOO_MUCH_CANDY
      FROM REL_B
      WHERE REL_B.ID = :NEW.ID
      AND :NEW.CANDY > REL_B.TOO_MUCH;
    IF (V_COUNT_TOO_MUCH_CANDY > 0)
      THEN
      RAISE_APPLICATION_ERROR(-20144,'Stop eating!');
    END IF;
  END;
  /

Then test:

These are ok:

INSERT INTO REL_A VALUES (1,9);
INSERT INTO REL_A VALUES (1,10);

But this is blocked:

INSERT INTO REL_A VALUES (1,11);
Error starting at line : 1 in command -
INSERT INTO REL_A VALUES (1,11)
Error report -
ORA-20144: Stop eating!

score 1 · Answer 2 · edited May 23 '17 at 12:32

1

On the line after END;, enter a single slash (/) to tell SQLPlus to execute the current command buffer.

See also When do I need to use a semicolon vs a slash in Oracle SQL?

(There may be other issues with your code, and the other answers may address them. But I believe this is your immediate problem in terms of actually telling SQLPlus to compile and create the trigger.)

edited May 23 '17 at 12:32

Community

1
1

answered Mar 27 '17 at 23:36

Dave Costa

47,262
8
56
72

score 0 · Answer 3 · answered Mar 27 '17 at 23:33

I believe you intend this:

CREATE TRIGGER CandyCons ON Rel_A
BEFORE INSERT, UPDATE
AS
    IF EXISTS (SELECT 1 FROM Rel_B B WHERE :NEW.ID = B.ID AND :NEW.candy > B.too_much
              )
    BEGIN
        RAISERROR ('Stop eating!', 16, 1);
        ROLLBACK TRANSACTION;
    RETURN; 
END;

Wait. Oracle doesn't support if exists. And it requires then. So:

CREATE TRIGGER CandyCons ON Rel_A BEFORE INSERT, UPDATE
DECLARE
    v_cnt number;
BEGIN
    SELECT COUNT(*) INTO v_cnt
    FROM Rebl_B B
    WHERE :NEW.ID = B.ID AND :NEW.candy > B.too_much;
    IF v_cnt = 0 THEN
    BEGIN
        RAISERROR ('Stop eating!', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN; 
    END;
END;

Trigger not ending execution or input

3 Answers3