| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Conditional Trigger
Comments embedded.
On Nov 20, 6:14 am, mowi..._at_hotmail.com wrote:
> Hello all,
>
> I have the following task: I have a table with two fields, ISBN
> (NUMBER 9), TITLE and PERIODICAL_OWNER (NUMBER 9).
Appears to be three columns to me.
> I am trying to
> create a trigger that checks on insert, that the ISBN being entered is
> not already in the table but if it does, it must exist in the
> PERIODICAL_OWNER column.
Why use a trigger, unless this is a classroom assignment? A trigger won't capture any 'violations' in a set of inserts in a single transaction, thus you could have duplicate records inserted when initially populating this table and the trigger won't 'see' the 'error' and happily allow the duplicates to exist:
SQL> set echo off
Table created.
Trigger created.
No errors.
SQL>
SQL> insert all
2 into periodical
3 values (1590596366, 'Cost-Based Oracle Fundamentals', null)
4 into periodical
5 values (1590595300, 'Expert Oracle Database Architecture', null)
6 into periodical
7 values (1590596366, 'Cost-Based Oracle Fundamentals', null)
8 select *
9 from dual;
3 rows created. -- Note the first and third records inserted are the same which SHOULD be an error but is not as the trigger didn't catch it
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> insert all
2 into periodical
3 values (1590596366, 'Cost-Based Oracle Fundamentals', null)
4 into periodical
5 values (1590595300, 'Expert Oracle Database Architecture', null)
6 into periodical
7 values (1590596366, 'Cost-Based Oracle Fundamentals', null)
8 select *
9 from dual;
into periodical
*
ERROR at line 2:
ORA-21000: error number argument to raise_application_error of -1422
is out of
range
ORA-06512: at "BING.VAL_ISBN", line 28
ORA-04088: error during execution of trigger 'BING.VAL_ISBN' -- This
is basically a 'single-row subquery returns more than one row' error
because of the situation described above
SQL> I intentionally did not show the trigger code since this is apparently an assignment you need to complete. Some hints to help you:
*) If there are no rows in the table there is nothing to check.
*) :new and :old reference the current record, not all records in the
table
*) You'll need to select the isbn from the table where it
matches :new.isbn
*) For existing isbn values you'll need to
compare :new.periodical_owner to :new.isbn
*) You'll need to handle the situation where periodical_owner is NULL
*) This will need to use the autonomous_transaction pragma unless you
write a function
to return the existing isbn and call that from your trigger
For the uninitiated this is not a simple trigger to write.
Ridding ourselves of the duplicate record for the initial load:
SQL> set echo off serveroutput on size 1000000
Table created.
Trigger created.
No errors.
SQL> SQL> -- Initial load SQL> insert all
2 rows created.
SQL>
SQL> commit;
Commit complete.
SQL> SQL> -- Set of 'bad' insert attempts SQL> insert all
*
ERROR at line 2:
ORA-20070: Existing ISBN and PERIODICAL_OWNER must be equal for
existing ISBN
values
ORA-06512: at "BING.VAL_ISBN", line 26
ORA-04088: error during execution of trigger 'BING.VAL_ISBN'
SQL>
SQL> insert all
2 into periodical
3 values (1590596366, 'Cost-Based Oracle Fundamentals', 4443215678)
4 into periodical
5 values (1590595300, 'Expert Oracle Database Architecture',
9876543210)
6 select * from dual;
into periodical
*
ERROR at line 2:
ORA-20070: Existing ISBN and PERIODICAL_OWNER must be equal for
existing ISBN
values
ORA-06512: at "BING.VAL_ISBN", line 26
ORA-04088: error during execution of trigger 'BING.VAL_ISBN'
SQL> SQL> -- Set of 'good' inserts SQL> insert all
2 rows created.
SQL> So it works, sort of.
>
> So, if the new ISBN being entered is already in the table, it has to
> be in the PERIODICAL_OWNER column in order for it to be accepted. I am
> new to PL/SQL and have tried writing a trigger but it's not working.
> Here it is:
>
> IF :New.ISSN = :Old.ISSN And
That condition checks for an update to an existing record, it doesn't check the prospective insert against other records in the table. You'll need a select statement for that.
> :New.ISSN NOT IN (:Old.Tidsskriftnr_eier) THEN
> RAISE_APPLICATION_ERROR(-20070, 'ERROR>>' ||
> ' PERIODICAL:' ||
> ' For ISSN that already exists in the basen, it' ||
> ' must exist in periodicald to be accepted.');
> ELSE
> :New.ISSN := :New.ISSN;
> END IF;
>
Obviously there is more to this 'specification' than you've cared to share in your 'problem' description. Either provide all of the required information or expect less than useful help.
> Thank you in advance for your help
>
> --
> Me
David Fitzjarrell Received on Tue Nov 20 2007 - 08:36:46 CST
 |
 |