And another one about PL/SQL

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Wed, 19 Jun 2002 12:27:02 +0100
Message-ID: <E2F6A70FE45242488C865C3BC1245DA7024066A3@lnewton.leeds.lfs.co.uk>

Ok, I've tested it now - it worked !!!

SET lines 300

--

• Create sites table with default categories
• This is the PARENT table
  --
  DROP TABLE sites CASCADE CONSTRAINTS Table dropped

CREATE TABLE sites(sitecode VARCHAR2(6), category VARCHAR2(30)) Table created

ALTER TABLE sites ADD CONSTRAINT pk_sites PRIMARY KEY (sitecode) Table altered

INSERT INTO sites VALUES ('BRITPK','XXX') 1 row inserted

INSERT INTO sites VALUES ('OZPK','YYY')
1 row inserted

COMMIT
Commit complete

--

• Create the CHILD table to hold the site features
  --
  DROP TABLE sitefeatures CASCADE CONSTRAINTS Table dropped

CREATE TABLE sitefeatures(blah INTEGER, sitecode VARCHAR2(6), category VARCHAR2(30))
Table created

ALTER TABLE sitefeatures ADD CONSTRAINT pk_sitefeatures PRIMARY KEY (blah)
Table altered

ALTER TABLE sitefeatures ADD CONSTRAINT ri_sites_sitefeatures FOREIGN KEY (sitecode) REFERENCES sites (sitecode) ON DELETE CASCADE Table altered

--

• Trigger to obtain the default category based upon sitecode
  --
  CREATE OR REPLACE TRIGGER FEATURE_DETAILS BEFORE INSERT ON SITEFEATURES FOR EACH ROW BEGIN IF (:NEW.category IS NULL) THEN BEGIN SELECT category INTO :NEW.category FROM sites WHERE sitecode=:NEW.sitecode; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20001, 'SITECODE : '|| :NEW.sitecode || ' is unknown'); END; END IF; END; / Trigger created

--

• Tests which will work
  --
  INSERT INTO sitefeatures (blah, sitecode) VALUES (1, 'BRITPK') 1 row inserted

INSERT INTO sitefeatures (blah, sitecode) VALUES (2, 'OZPK') 1 row inserted

INSERT INTO sitefeatures (blah, sitecode, category) VALUES (3, 'BRITPK', 'THIS IS NOT THE DEFAULT')
1 row inserted

COMMIT
Commit complete

--

• Test which will NOT work
  --
  INSERT INTO sitefeatures (blah, sitecode) VALUES (4, 'BUMMER')

ORA-20001: SITECODE : BUMMER is unknown
ORA-06512: at "SYSTEM.FEATURE_DETAILS", line 7
ORA-04088: error during execution of trigger 'SYSTEM.FEATURE_DETAILS'

--

• Check results
  --
  SELECT * FROM sites

SITECODE CATEGORY
-------- ------------------------------

BRITPK   XXX                           
OZPK     YYY                           

2 rows selected

SELECT * FROM sitefeatures

                                   BLAH SITECODE CATEGORY



--------------------------------------- --------

------------------------------

                                      1 BRITPK   XXX

                                      2 OZPK     YYY

                                      3 BRITPK   THIS IS NOT THE DEFAULT

3 rows selected

Nice one Skippy !

Regards,
Norman.

---

Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar_at_LFS.co.uk

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com


-------------------------------------

Received on Wed Jun 19 2002 - 06:27:02 CDT