Trigger with lookup from another table

Question

I'm learning PL/SQL specifically triggers, and I want to learn how to operate on multiple tables using triggers but I'm having trouble understanding the process. Here's an example:

CREATE TABLE Sess
(code       INTEGER     NOT NULL,
 dateStart  DATE        NOT NULL,
 dateEnd    DATE        NOT NULL,
 CONSTRAINT KeySess PRIMARY KEY(code)
)

CREATE TABLE Ins
(codeIns    CHAR(12)    NOT NULL,
 code       INTEGER     NOT NULL,
 dateIns    DATE        NOT NULL,
 dateAb     DATE,
 note       INTEGER,
 CONSTRAINT KeyIns PRIMARY KEY (codeIns, code)
)

1/ I'm trying to build a trigger that will ensure this:

before Insert or Update Ins

dateAb is between dateStart and dateEnd

2/ I want also to ALTER TABLE and lock code, and codeIns in Ins so no one can update them but i don't know the command.

For the first trigger, I tried something like:

CREATE OR REPLACE TRIGGER test
BEFORE INSERT OR UPDATE OF dateAb ON Ins
FOR EACH ROW
DECLARE
    start date;
BEGIN
    SELECT DISTINCT s.dateStart INTO start
    WHERE Ins.code = s.code
    IF (:NEW.dateAb < start) THEN
        raise_application_error(-20101,'dateAb incorrect');
    END IF;
END;

Note: I didn't declare a variable end to check if it's between, my purpose here was to test the correct syntax

Note2: the table are empty.

I had Warning: Trigger created with compilation errors. and 3 errors :

PL/SQL: SQL Statement ignored
4/40    PL/SQL: ORA-00923: key-word FROM absent 
9/5     PLS-00103: Encountered the symbol "IF" when expecting one of the
        following:
        ;

And finally like I said I don't know the correct syntax to lock column on table to prevent updates or if it's possible.

I'm learning so if one of you can teach me the correct way to process with my two request, I would apreciate that. Thank you

Your `select` is missing `from sess s`. The `where` clause should probably be `where s.code = :new.code`. — William Robertson, Apr 21 '18 at 09:01
Also `start` is a SQL keyword (part of `connect by` syntax) so you can't use it as a variable. — William Robertson, Apr 21 '18 at 09:05
Also [that `CHAR` column should be a `VARCHAR2`](https://stackoverflow.com/a/42165653/230471). — William Robertson, Apr 21 '18 at 09:26

score 2 · Accepted Answer · answered Apr 21 '18 at 09:18

2

Try this:

create or replace trigger test
    before insert or update of dateab on ins
    for each row
declare
    l_sess_start date;
begin
    select s.datestart into l_sess_start
    from   sess s
    where  s.code = :new.code;

    if :new.dateab < l_sess_start then
        raise_application_error
        ( -20101
        , 'Start date ' || to_char(:new.dateab,'DD-MON-YYYY') ||
          ' cannot be before ' || to_char(l_sess_start,'DD-MON-YYYY'));
    end if;
end;

Test:

insert into sess values (1, date '2018-04-22', date '2018-04-30');

insert into ins values ('Z', 1, date '2018-04-01', date '2018-04-01', null);

ERROR at line 1:
ORA-20101: Start date 01-APR-2018 cannot be before 22-APR-2018
ORA-06512: at "WILLIAM.TEST", line 10
ORA-04088: error during execution of trigger 'WILLIAM.TEST'

answered Apr 21 '18 at 09:18

William Robertson

15,273
4
38
44

Worked thanks !! , Is there a way to lock column on table in order to prevent updates ? like `ALTER TABLE` and lock code, and codeIns. – Haskell-newb Apr 21 '18 at 09:34
You can do it with a trigger - raise an error if the column is updated – David Faber Apr 21 '18 at 12:58
If you don't want users to update a table, don't grant them `update` privilege. – William Robertson Apr 22 '18 at 10:59

score 0 · Answer 2 · answered Apr 21 '18 at 08:38

0

PLS-00103: Encountered the symbol "IF" when expecting one of the
           following:
           ;

This error is because you are missing ; after select statement

answered Apr 21 '18 at 08:38

Rahul Jain

1,319
7
16

Trigger with lookup from another table

2 Answers2