0

Homework requirement: create a trigger where account must belong to one and only one customer.

My code as shown below doesn't work. Error mesg: quoted string not properly terminated. Please help.

create or replace trigger dupcust
before insert or update on ACCOUNT
for each row
declare
    v_ctn NUMBER;
begin
    select count(account.cname) into v_ctn from account where A#=:new.A#;
    if v_ctn>0 then  
        raise_application_error (-20107, 'ACCT CAN ONLY BELONG TO ONE CUSTOMER');
    end if;
end;

Test code:

UPDATE ACCOUNT SET ACCOUNT.CNAME =’Cook’ WHERE ACCOUNT.A# = ‘1111’;
Jie
  • 65
  • 6
  • Shouldn't `A#` be the primary key of ACCOUNT? So it should be impossible to have more than one record per account number, so impossible for an Account to have more than one Customer. – APC Apr 08 '17 at 16:44

1 Answers1

2

When I copied your query, it failed due to the quotes around the string cook and 1111. You apparently used the wrong quote character, I corrected the quotes and when I run the query, the trigger generates a mutation error, the answer to that can can be found here:

PLSQL Trigger Error. "is mutating, trigger/function may not see it" ORA-04091

Basically you are reading from the table you are updating, that causes a mutation, you can't do that.

A compound trigger, if written correctly, would do the job, an example is here:

Oracle trigger after insert or delete

Community
  • 1
  • 1
Prescott Chartier
  • 1,519
  • 3
  • 17
  • 34
  • Hi Prescott, thank you so much for your comments and citations! I added pragma autonomous_transaction; in the declaration part and the trigger worked. Im wondering what this pragma autonomous_transaction does. – Jie Apr 08 '17 at 19:55
  • You can read about pragma autonomous_transaction here: https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/autonotransaction_pragma.htm, basically it allows you to manipulate data inside the transactions without affecting data outside the transaction. – Prescott Chartier Apr 09 '17 at 12:01
  • Autonomous transactions are not the right solution to this problem. The trigger code wouldn't see the newly inserted/updated rows in the calling session, so the "validation" will only succeed by accident. – Jeffrey Kemp Apr 10 '17 at 07:30