Re: Does/will Oracle CASE generate procedures and dbs triggers?

From: Grant Johnson <445362_at_aisserver2.llnl.gov>
Date: 13 Aug 93 09:55:00
Message-ID: <445362.93Aug13095500_at_aisserver2.llnl.gov>

Heres what we developed:

            Developing with Oracle CASE 5.0 and RDBMS 7.0

While Oracle CASE 5.0 doesn't support Oracle 7 features such as database triggers and stored procedures, it does provide a repository for integrity constraints. And in the hope that CASE 5.1 is eventually available, we would like to use CASE now.

The Development Cycle

---

		  CASE - Maintain object definitions
				  |
				  V
			     Generate DDL
				  |
				  V
			      Modify DDL
				  |
				  V
			  Execute Master DDL

The Master DDL script is written manually and is of the following basic outline:

	DROP tables, sequences
	SET COMPATIBILTY TO V6

	START cdcddl.sql



	{Enable Constraints}



	SET COMPATIBILTY TO V7


	{Create Triggers, Procedures & other V7 specific objects.}





	{Create Roles}

CASE Notes

Constraint	Notes
----------	--------------------------------------------------
Primary Key	Don't define index in CASE.  V7 will create it.
Foreign Key	If you need ON DELETE CASCADE, modify the CASE DDL
		manually.
Unique Key	No changes.
Not Null	May be a problem with some triggers.  See below.
Checks		No changes.

Note that while the above are created, they are not enabled. A script needs to be written that will enable all constraints.

Integrity Rule Considerations

---

Integrity rules seem to be evaluated before triggers are fired. This caused us a problem with audit trail info. We would like to be able to have audit fields defined as NOT NULL and populated with a BEFORE INSERT trigger. The problem is the parser sees the NOT NULL constraint before the trigger has populated the field. The options are to include a dummy values or enforce the NOT NULL in the trigger.

Sequence generators populating an internal key have a similar problem. Our first strategy was to have a BEFORE INSERT trigger populate the internal key from a sequence. First we got an error from SQL*Forms because the field was null on insert. We got around this by defaulting the field to 0, knowing the value would be overwritten by the trigger. However, because the trigger has no way to send the internal number back to the form, the internal number there stays at 0. Then when the associated detail rows are inserted, they use 0 as their internal number. Of course this violates the foreign key constraint and aborts the transaction. The solution: do it the normal way and populate the internal number from the form.

Security

---

GRANT objects to PUBLIC WITH GRANT OPTION

	Without the 'WITH GRANT OPTION', other users objects cannot be
	included in views.

GETUID user exit is no longer needed as OPS$ accounts will have update
	access. 

We are just beginning to use CASE 5.1 and it does support definitions for triggers and procedures. We'll post more as we learn... Received on Fri Aug 13 1993 - 09:55:00 CEST