CODEWITHSUNDEEP
oracle-databaseplsqltriggers
Lisa
Lisa

Reputation: 3181

Warning: Procedure created with compilation errors?

Why this is giving me errors?

CREATE OR REPLACE trigger customerLineCount
BEFORE insert on  cust_line 
for each row
when(new.cust_id > 0)
DECLARE
    lineCount number;
BEGIN
    select count (*) into lineCount
    from  (cust_line inner join  customer
    on cust_line.cust_id = customer.cust_id)
    where (customer.cust_id = :new.cust_id)

    if :new.gender = "m" and lineCount = 3 THEN
        dbms_output.put_line ('Error! User already has 3 lines');
    elseif :new.gender = "f" and lineCount = 1 THEN
        dbms_output.put_line ('Error! User already has 1 line');
    end if;
END  customerLineCount;
/

Upvotes: 0

Views: 4602

Answers (2)

Justin Cave
Justin Cave

Reputation: 231651

1) A string in PL/SQL is delimited by single quotes, not double quotes. So if you want to check what the gender is, you'd need something like

if :new.gender = 'm' and lineCount = 3 THEN
    dbms_output.put_line ('Error! User already has 3 lines');
elseif :new.gender = 'f' and lineCount = 1 THEN
    dbms_output.put_line ('Error! User already has 1 line');
end if;

2) Your SELECT statement is missing a semicolon at the end.

3) Once you resolve the compilation errors, however, you're almost certainly going to encounter a runtime error. In general, a row-level trigger on a table cannot query that same table. So your row-level trigger on cust_line cannot query the cust_line table. You can potentially work around that by creating a package which contains a PL/SQL collection and then creating multiple triggers. A before statement trigger would initialize the collection, a row-level trigger would fill the collection with the keys from the newly inserted rows. And then an after statement trigger would iterate through the collection, query the table, and apply whatever business logic you want. This, however, is a very complicated approach that is rarely necessary in practice. You are generally much better served by using constraints rather than triggers or by enforcing the business rules in the stored procedure that does the inserts.

Upvotes: 5

user330315
user330315

Reputation:

You are missing a semicolon after the first select in the body.

Upvotes: 3

Related Questions