Skip to Main Content

DM 3.0.0.665 instead of triggers not generating DDL correctly

851664Mar 30 2011 — edited Aug 8 2011
There seems to be a bug in the DDL generation for manually created INSTEAD OF triggers on views. The INSTEAD OF trigger does not have the INSTEAD OF clause upon DDL generation. Instead, it has the BEFORE clause. It seems to be treating INSTEAD OF triggers as normal triggers. Note: When I reversed engineered a database that already had an INSTEAD OF trigger, it imported and generated DDL was fine. This bug shows itself when manually creating INSTEAD OF triggers.

Steps to replicate:
1. In any design that has a view.
2. In the physical model, find the view, expand it, right click to create Trigger for view
3. By definition, a trigger on a view can only be an instead of trigger.
4. The dialog box will have the 'Triggering time' showing as INSTEAD OF. You can't change it. (Which there is no reason to do anyway)
5. Generate DDL. When generating the DDL, the trigger will not be generated as INSTEAD OF, it will be generated as a BEFORE (INSERT/UPDATE/DELETE).

Included DDL generation from a simple database (I did not modify the DDL generated):

-- Generated by Oracle SQL Developer Data Modeler 3.0.0.665
-- at: 2011-03-30 13:16:32 EDT
-- site: Oracle Database 10g
-- type: Oracle Database 10g



-- CREATE DATABASE DB2
-- CONTROLFILE REUSE
-- MAXLOGFILES 1
-- MAXLOGMEMBERS 1
-- MAXLOGHISTORY 0
-- MAXDATAFILES 10
-- MAXINSTANCES 1
-- ARCHIVELOG
-- FORCE LOGGING
-- DATAFILE
-- '' SIZE 0 K REUSE
CREATE TABLE TABLE_1
(
Column_1 VARCHAR2 (1)
) LOGGING
;




CREATE OR REPLACE VIEW VIEW_1 ( Column_1 )
AS SELECT
TABLE_1.Column_1
FROM
TABLE_1 TABLE_1 ;



CREATE OR REPLACE TRIGGER Trg1
BEFORE INSERT ON VIEW_1
FOR EACH ROW
BEGIN
NULL;
END;
/






-- Oracle SQL Developer Data Modeler Summary Report:
--
-- CREATE TABLE 1
-- CREATE INDEX 0
-- ALTER TABLE 0
-- CREATE VIEW 1
-- CREATE PACKAGE 0
-- CREATE PACKAGE BODY 0
-- CREATE PROCEDURE 0
-- CREATE FUNCTION 0
-- CREATE TRIGGER 1
-- CREATE STRUCTURED TYPE 0
-- CREATE COLLECTION TYPE 0
-- CREATE CLUSTER 0
-- CREATE CONTEXT 0
-- CREATE DATABASE 1
-- CREATE DIMENSION 0
-- CREATE DIRECTORY 0
-- CREATE DISK GROUP 0
-- CREATE ROLE 0
-- CREATE ROLLBACK SEGMENT 0
-- CREATE SEQUENCE 0
-- CREATE MATERIALIZED VIEW 0
-- CREATE SYNONYM 0
-- CREATE TABLESPACE 0
-- CREATE USER 0
--
-- DROP TABLESPACE 0
-- DROP DATABASE 0
--
-- ERRORS 0
-- WARNINGS 0
Comments
Post Details
Added on Mar 30 2011
2 comments
77 views