Home » SQL & PL/SQL » SQL & PL/SQL » Create a combination DML Trigger (Oracle 11gr2, multiple OS platform)
| Create a combination DML Trigger [message #667360] |
Sun, 24 December 2017 20:18  |
trantuananh24hg
Messages: 743
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Dear all,
I have 2 tables, one is MNP_SUB, the other is MNP_SERVICE. Those tables has same structure and both are heap tables
mnp@MNP> desc mnp_sub
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ID NOT NULL NUMBER
MSISDN NOT NULL VARCHAR2(20)
CUSTOMER_ID NUMBER
SUB_TYPE NUMBER
IS_PRIMARY VARCHAR2(1)
NAME VARCHAR2(100)
REGISTRATION_NAME VARCHAR2(100)
DOC_TYPE VARCHAR2(5)
DOC_NUMBER VARCHAR2(100)
DOC_ISSUE_DATE DATE
DOC_ISSUE_PLACE VARCHAR2(500)
SUB_REPRESENTATIVE VARCHAR2(100)
CONTRACT_NUMBER VARCHAR2(100)
DNO_CONTRACT_NUMBER VARCHAR2(100)
ADDRESS_ID VARCHAR2(1500)
EMAIL VARCHAR2(100)
CONTACT_NUMBER VARCHAR2(100)
SUB_STATUS NUMBER
ORIGIN_PARTICIPANT VARCHAR2(2)
CURRENT_PARTICIPANT NOT NULL VARCHAR2(2)
RECIPIENT_PARTICIPANT VARCHAR2(2)
COMMENTS VARCHAR2(2000)
CREATED_DATE DATE
CREATED_BY VARCHAR2(100)
MODIFIED_DATE DATE
MODIFIED_BY VARCHAR2(100)
CREATE_BY VARCHAR2(100 CHAR)
CREATE_DATE TIMESTAMP(6)
REGISTRAION_NAME VARCHAR2(200 CHAR)
VERSION NUMBER(10)
mnp@MNP> desc mnp_service
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ID NOT NULL NUMBER
MSISDN VARCHAR2(20)
CUSTOMER_ID NUMBER
SUB_TYPE NUMBER
IS_PRIMARY VARCHAR2(1)
NAME VARCHAR2(100)
REGISTRATION_NAME VARCHAR2(100)
DOC_TYPE VARCHAR2(5)
DOC_NUMBER VARCHAR2(100)
DOC_ISSUE_DATE DATE
DOC_ISSUE_PLACE VARCHAR2(500)
SUB_REPRESENTATIVE VARCHAR2(100)
CONTRACT_NUMBER VARCHAR2(100)
DNO_CONTRACT_NUMBER VARCHAR2(100)
ADDRESS_ID VARCHAR2(1500)
EMAIL VARCHAR2(100)
CONTACT_NUMBER VARCHAR2(100)
SUB_STATUS NUMBER
ORIGIN_PARTICIPANT VARCHAR2(2)
CURRENT_PARTICIPANT VARCHAR2(2)
RECIPIENT_PARTICIPANT VARCHAR2(2)
COMMENTS VARCHAR2(2000)
CREATED_DATE DATE
CREATED_BY VARCHAR2(100)
MODIFIED_DATE DATE
MODIFIED_BY VARCHAR2(100)
I wrote a trigger after insert into MNP_SUB, then does insert into MNP_SERVICE last month.
create or replace TRIGGER mnp_service_trg
AFTER INSERT ON MNP_SUB
FOR EACH ROW
BEGIN
INSERT INTO MNP_SERVICE
(ID,msisdn,customer_id,sub_type,is_primary,name,registration_name,doc_type,doc_number,doc_issue_date,doc_issue_place,sub_representative,
contract_number,dno_contract_number,address_id,email,contact_number,sub_status,origin_participant,current_participant,recipient_participant,comments,
created_date,created_by,modified_date,modified_by)
values
(:new.ID,:new.msisdn,:new.customer_id,:new.sub_type,:new.is_primary,:new.name,:new.registration_name,:new.doc_type,:new.doc_number,:new.doc_issue_date,:new.doc_issue_place,:new.sub_representative,
:new.contract_number,:new.dno_contract_number,:new.address_id,:new.email,:new.contact_number,:new.sub_status,:new.origin_participant,:new.current_participant,:new.recipient_participant,:new.comments,
:new.created_date,:new.created_by,:new.modified_date,:new.modified_by);
END;
And now, I want to create a combination trigger, including AFTER DELETE, AFTER UPDATE and AFTER UPDATE MNP_SUB, affect to MNP_SERVICE. Only AFTER but not BEFORE.
The scenario is simple:
If insert any row into MNP_SUB, then insert into MNP_SERVICE
If update any row MNP_SUB, then update MNP_SERVICE
If delete any row MNP_SUB, then delete MNP_SERVICE
May you help me?
Thank you!
[Updated on: Sun, 24 December 2017 20:37] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Create a combination DML Trigger [message #667366 is a reply to message #667360] |
Mon, 25 December 2017 22:25  |
 |
Barbara Boehmer
Messages: 9058
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Please see the following simplified example that uses only 2 columns.
-- tables:
SCOTT@orcl_12.1.0.2.0> create table mnp_sub
2 (id number,
3 name varchar2(30))
4 /
Table created.
SCOTT@orcl_12.1.0.2.0> create table mnp_service
2 (id number,
3 name varchar2(30))
4 /
Table created.
-- trigger:
SCOTT@orcl_12.1.0.2.0> create or replace TRIGGER mnp_service_trg
2 AFTER INSERT OR UPDATE OR DELETE ON MNP_SUB
3 FOR EACH ROW
4 BEGIN
5 IF INSERTING THEN
6 INSERT INTO MNP_SERVICE
7 (ID,name)
8 values
9 (:new.ID,:new.name);
10 ELSIF UPDATING THEN
11 UPDATE mnp_service
12 SET id = :NEW.id,
13 name = :NEW.name
14 WHERE id = :OLD.id
15 AND name = :OLD.name;
16 ELSIF DELETING THEN
17 DELETE FROM mnp_service
18 WHERE id = :OLD.id
19 AND name = :OLD.name;
20 END IF;
21 END;
22 /
Trigger created.
SCOTT@orcl_12.1.0.2.0> show errors
No errors.
-- inserts, update, delete, and results:
SCOTT@orcl_12.1.0.2.0> insert into mnp_sub values (1,'name1')
2 /
1 row created.
SCOTT@orcl_12.1.0.2.0> insert into mnp_sub values (2,'name2')
2 /
1 row created.
SCOTT@orcl_12.1.0.2.0> insert into mnp_sub values (3,'name3')
2 /
1 row created.
SCOTT@orcl_12.1.0.2.0> update mnp_sub set name = 'name20' where id = 2
2 /
1 row updated.
SCOTT@orcl_12.1.0.2.0> delete from mnp_sub where id = 3
2 /
1 row deleted.
SCOTT@orcl_12.1.0.2.0> select * from mnp_sub
2 /
ID NAME
---------- ------------------------------
1 name1
2 name20
2 rows selected.
SCOTT@orcl_12.1.0.2.0> select * from mnp_service
2 /
ID NAME
---------- ------------------------------
1 name1
2 name20
2 rows selected.
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 23 19:35:43 CDT 2023
|
] 
Blog
Search
Help
Members
Register
Login
Home
