marrowyung
asked on
The Oracle triggers
Right now we are doing BEFORE delete/AFTER INSERT/AFTER update trigger for any Oralce DB, the MySQL one has been done:
what should be the correct version for Oracle 10g and 11g ?
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 ;
what should be the correct version for Oracle 10g and 11g ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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:
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.
"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 ;
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.
ASKER
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 ?
"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 ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
"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?
you just mean the trigger BELETE, INSERT and after can conbine in one trigger statement but just BEFORE or AFTER, right?
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_
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;