--1.CHECK
select trigger_name
, trigger_type
, table_name
, status
from dba_triggers
where owner = '&owner'
--2. TẠO Trigger:
CREATE OR REPLACE TRIGGER drop_trigger
BEFORE DROP ON hr.SCHEMA
BEGIN
RAISE_APPLICATION_ERROR (
num => -20000,
msg => 'Cannot drop object');
END;
/
-- Tạo bảng tạm:
CREATE TABLE user1.XXX_TAB1 as
SELECT a.*, '--' action,SYSDATE update_date FROM TAB1 a WHERE 1=0;
-- TAB1
CREATE OR REPLACE TRIGGER user1.TAB1_BI
BEFORE
INSERT
ON TAB1
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
Declare
BEGIN
INSERT INTO user1.XXX_TAB1
(ID,
SER_TYPE,
REG_DATE,
STA_DATETIME,
END_DATETIME,
USER_NAME,
STATUS,
EXPIRE_DATE,
SHOP_CODE,
ACT_STATUS,
ACTION,
UPDATE_DATE)
VALUES
(:New.ID,
:New.SER_TYPE,
:New.REG_DATE,
:New.STA_DATETIME,
:New.END_DATETIME,
:New.USER_NAME,
:New.STATUS,
:New.EXPIRE_DATE,
:New.SHOP_CODE,
:New.ACT_STATUS,
'BI',
SYSDATE);
End;
CREATE OR REPLACE TRIGGER user1.TAB1_BU
BEFORE
UPDATE
ON TAB1
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
Declare
BEGIN
INSERT INTO user1.XXX_TAB1
(ID,
SER_TYPE,
REG_DATE,
STA_DATETIME,
END_DATETIME,
USER_NAME,
STATUS,
EXPIRE_DATE,
SHOP_CODE,
ACT_STATUS,
ACTION,
UPDATE_DATE)
VALUES
(:New.ID,
:New.SER_TYPE,
:New.REG_DATE,
:New.STA_DATETIME,
:New.END_DATETIME,
:New.USER_NAME,
:New.STATUS,
:New.EXPIRE_DATE,
:New.SHOP_CODE,
:New.ACT_STATUS,
'BU',
SYSDATE);
End;
CREATE OR REPLACE TRIGGER user1.TAB1_BD
BEFORE
DELETE
ON TAB1
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
Declare
BEGIN
INSERT INTO user1.XXX_TAB1
(ID,
SER_TYPE,
REG_DATE,
STA_DATETIME,
END_DATETIME,
USER_NAME,
STATUS,
EXPIRE_DATE,
SHOP_CODE,
ACT_STATUS,
ACTION,
UPDATE_DATE)
VALUES
(:New.ID,
:New.SER_TYPE,
:New.REG_DATE,
:New.STA_DATETIME,
:New.END_DATETIME,
:New.USER_NAME,
:New.STATUS,
:New.EXPIRE_DATE,
:New.SHOP_CODE,
:New.ACT_STATUS,
'BD',
SYSDATE);
End;
--3.ALTER TRIGGER
• Disabling Triggers:
ALTER TRIGGER update_job_history DISABLE;
• Enabling Triggers:
ALTER TRIGGER update_job_history ENABLE;
--4.DROP TRIGGER
DROP TRIGGER hr.salary_check;
