Solved

The Oracle triggers

Posted on 2014-01-29
8
447 Views
Last Modified: 2014-03-02
Right now we are doing BEFORE delete/AFTER INSERT/AFTER update trigger for any Oralce DB, the MySQL one has been done:

 DROP TRIGGER IF EXISTS PACKAGE_ELEMENT_BEFORE_DELETE_TRIGGER; CREATE TRIGGER PACKAGE_ELEMENT_BEFORE_DELETE_TRIGGER BEFORE DELETE ON PACKAGE_ELEMENT FOR EACH ROW BEGIN  INSERT INTO Audit_info.PCCWSP_DBAudit_PACKAGE_ELEMENT SET  TriggerAction="BEFORE", ActionDone="DELETE", ActionDate=now(), ActionBy=USER() , PACKAGE_ID=OLD.PACKAGE_ID, ELEMENT_ID=OLD.ELEMENT_ID, PRODUCT_FAMILY=OLD.PRODUCT_FAMILY, PRODUCT_TYPE=OLD.PRODUCT_TYPE, VENDOR_TYPE=OLD.VENDOR_TYPE, VENDOR_ID=OLD.VENDOR_ID, ITEM_CATEGORY=OLD.ITEM_CATEGORY, ITEM_SUBCATEGORY=OLD.ITEM_SUBCATEGORY, NOMENCLATURE=OLD.NOMENCLATURE, FROM_COUNTRY=OLD.FROM_COUNTRY, FROM_CITY=OLD.FROM_CITY, FROM_POP_ID=OLD.FROM_POP_ID, TO_COUNTRY=OLD.TO_COUNTRY, TO_CITY=OLD.TO_CITY, TO_POP_ID=OLD.TO_POP_ID, FROM_SITE_TYPE=OLD.FROM_SITE_TYPE, TO_SITE_TYPE=OLD.TO_SITE_TYPE, FROM_BANDWIDTH=OLD.FROM_BANDWIDTH, FROM_BW_UNIT=OLD.FROM_BW_UNIT, TO_BANDWIDTH=OLD.TO_BANDWIDTH, TO_BW_UNIT=OLD.TO_BW_UNIT, PARTNERSHIP_MODEL=OLD.PARTNERSHIP_MODEL, PROTECTION=OLD.PROTECTION, RESTORABLE=OLD.RESTORABLE, CREATED_DT=OLD.CREATED_DT, CREATED_BY=OLD.CREATED_BY, UPDATED_DT=OLD.UPDATED_DT, UPDATED_BY=OLD.UPDATED_BY, AAA=OLD.AAA, AAB=OLD.AAB; END ; 

Open in new window


what should be the correct version for Oracle 10g and 11g ?
0
Comment
Question by:marrowyung
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
8 Comments
 
LVL 37

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 200 total points
ID: 39820261
in oracle you can use "create or replace"
if the trigger exists, it will replace the existing one, if not, it will create it
CREATE OR REPLACE TRIGGER PACKAGE_ELEMENT_BEFORE_DELETE_TRIGGER 
BEFORE DELETE ON PACKAGE_ELEMENT 
FOR EACH ROW 
BEGIN  
  INSERT INTO Audit_info.PCCWSP_DBAudit_PACKAGE_ELEMENT 
  SET  TriggerAction="BEFORE", ActionDone="DELETE", ActionDate=SYSDATE, 
    ActionBy=USER , PACKAGE_ID=:OLD.PACKAGE_ID, 
    ELEMENT_ID=:OLD.ELEMENT_ID, 
    PRODUCT_FAMILY=:OLD.PRODUCT_FAMILY, 
    PRODUCT_TYPE=:OLD.PRODUCT_TYPE, 
    etc ... etc ...
END ;

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39820611
In oracle you can do delete, insert and update all in one trigger.
But change the BEFORE DELETE to AFTER DELETE (it shouldn't matter)

The INSERT syntax is different in Oracle, you don't use SET

CREATE TRIGGER trg_package_element_ariud
    AFTER DELETE OR INSERT OR UPDATE
    ON package_element
    FOR EACH ROW
BEGIN
    INSERT INTO audit_info.pccwsp_dbaudit_package_element(
                    triggeraction,
                    actiondone,
                    actiondate,
                    actionby,
                    package_id,
                    element_id,
                    product_family,
                    product_type,
                    vendor_type,
                    vendor_id,
                    item_category,
                    item_subcategory,
                    nomenclature,
                    from_country,
                    from_city,
                    from_pop_id,
                    to_country,
                    to_city,
                    to_pop_id,
                    from_site_type,
                    to_site_type,
                    from_bandwidth,
                    from_bw_unit,
                    to_bandwidth,
                    to_bw_unit,
                    partnership_model,
                    protection,
                    restorable,
                    created_dt,
                    created_by,
                    updated_dt,
                    updated_by,
                    aaa,
                    aab
                )
         VALUES (
                    'AFTER',
                    CASE
                            WHEN INSERTING THEN 'INSERT'
                            WHEN UPDATING THEN 'UPDATE'
                            ELSE 'DELETE'
                        END,
                    SYSDATE,
                    USER,
                    :old.package_id,
                    :old.element_id,
                    :old.product_family,
                    :old.product_type,
                    :old.vendor_type,
                    :old.vendor_id,
                    :old.item_category,
                    :old.item_subcategory,
                    :old.nomenclature,
                    :old.from_country,
                    :old.from_city,
                    :old.from_pop_id,
                    :old.to_country,
                    :old.to_city,
                    :old.to_pop_id,
                    :old.from_site_type,
                    :old.to_site_type,
                    :old.from_bandwidth,
                    :old.from_bw_unit,
                    :old.to_bandwidth,
                    :old.to_bw_unit,
                    :old.partnership_model,
                    :old.protection,
                    :old.restorable,
                    :old.created_dt,
                    :old.created_by,
                    :old.updated_dt,
                    :old.updated_by,
                    :old.aaa,
                    :old.aab
                );
END;
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39832730
sdstuber,

"But change the BEFORE DELETE to AFTER DELETE "

so there are no before delete ?

Also in MySQL, I use the following function to dynamically generate the BEFORE DELETE trigger statement:

CREATE FUNCTION `Function_Create_BEFORE_DELETE_TRIGGER` (Trigger_databasename VARCHAR(40),  trigger_tablename VARCHAR(40))  	
RETURNS TEXT
NOT DETERMINISTIC      
READS SQL DATA
BEGIN  
DECLARE a, b, fieldnameCursor_finished  INT DEFAULT 10;
DECLARE temptable VARCHAR(50);	
DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;
DECLARE v1, v2, v3 TINYINT ;
DECLARE current_fieldname  VARCHAR(50) DEFAULT ""; 
/* DECLARE fullexcutecmd VARCHAR(65535);
DECLARE fullexcutecmd1 VARCHAR(5000);
DECLARE fullexcutecmd2 VARCHAR(5000);
DECLARE fullexcutecmd3 VARCHAR(60000);*/
DECLARE fullexcutecmd TEXT;
DECLARE fullexcutecmd1 TEXT;
DECLARE fullexcutecmd2 TEXT;
DECLARE fullexcutecmd3 TEXT;

/*Declare and populate the cursor with a SELECT statement */  	
	  
DECLARE fieldname CURSOR FOR 	
SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = Trigger_databasename AND TABLE_NAME = trigger_tablename ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET fieldnameCursor_finished = 1;     

  
SET @fullexcutecmd1= CONCAT( '  DROP TRIGGER IF EXISTS ',trigger_tablename,'_BEFORE_DELETE_TRIGGER; CREATE TRIGGER ',trigger_tablename,'_BEFORE_DELETE_TRIGGER BEFORE DELETE ON ',trigger_tablename,' FOR EACH ROW
BEGIN 
INSERT INTO Audit_info.',Trigger_databasename,'_DBAudit_',trigger_tablename,' SET 
TriggerAction=','"','BEFORE','",','
ActionDone=','"','DELETE','",','
ActionDate=now(),
ActionBy=USER()');   

OPEN fieldname ;	

set @fullexcutecmd2=' ';
set @fullexcutecmd3=' ';

	get_fieldlist: LOOP	
 
   FETCH fieldname INTO current_fieldname;	
         IF fieldnameCursor_finished = 1 THEN 	
          LEAVE get_fieldlist;	
          END IF;	
 
SET @fullexcutecmd2 = CONCAT(' ',current_fieldname,'=OLD.',current_fieldname);
 set @fullexcutecmd3= CONCAT(@fullexcutecmd3,',',@fullexcutecmd2); 

      END LOOP  get_fieldlist;	
close fieldname;                             
  
  set @fullexcutecmd3= CONCAT(@fullexcutecmd1,@fullexcutecmd3,'; END ;'); 

  
END $$
DELIMITER ; 

Open in new window


is there anyway for Oracle to get dynamic SQL statement to construct that AFTER/BEFORE DELETE trigger?

Can it automatically execute as MySQL can't execute that create trigger statements itself.
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 1

Author Comment

by:marrowyung
ID: 39846588
sdstuber,

"AFTER DELETE OR INSERT OR UPDATE"

So this mean we are now doing AFTRE delete/insert/update for this trigger? but we can't just do "BFFOR DELETE OR INSERT OR UPDATE" at the same time ?
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 300 total points
ID: 39878439
>>> So this mean we are now doing AFTRE delete/insert/update for this trigger? but we can't just do "BFFOR DELETE OR INSERT OR UPDATE" at the same time ?

no, a trigger is either BEFORE or AFTER an event.

Based on your examples and descriptions above it makes the most sense to just use a single AFTER trigger.

This avoids complications with statement restart or rollbacks for mid statement failures.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39898663
"no, a trigger is either BEFORE or AFTER an event."

you just mean the trigger BELETE, INSERT and after can conbine in one trigger statement but just BEFORE or AFTER, right?
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
get count of orders by customer Sql Server table. 3 48
Oracle statistik 3 17
Unidentified Function 2 34
oracle _plsql 7 17
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question