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 Go to next message
trantuananh24hg
Messages: 708
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 #667363 is a reply to message #667360] Mon, 25 December 2017 08:25 Go to previous messageGo to next message
EdStevens
Messages: 888
Registered: September 2013
Senior Member
Why are you even bothering to keep to identical tables in the first place? This sound like a serious design flaw.
Re: Create a combination DML Trigger [message #667365 is a reply to message #667363] Mon, 25 December 2017 22:17 Go to previous messageGo to next message
trantuananh24hg
Messages: 708
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
@EdStevens:
Yes, I knew, this was not clear design flow processing.
So, I have to redesign it. Thank you!
Re: Create a combination DML Trigger [message #667366 is a reply to message #667360] Mon, 25 December 2017 22:25 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8790
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.

Re: Create a combination DML Trigger [message #667367 is a reply to message #667366] Mon, 25 December 2017 22:31 Go to previous messageGo to next message
trantuananh24hg
Messages: 708
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Thank you, Barbara!
Re: Create a combination DML Trigger [message #667394 is a reply to message #667360] Tue, 26 December 2017 12:22 Go to previous message
joy_division
Messages: 4817
Registered: February 2005
Location: East Coast USA
Senior Member
What about simply?

create or replace view MNP_SERVICE as select A.Id, A.etc. from MNP_SUB A;

and name only the columns you need. I don't see anything in your post that says this will not suffice. you either left something out or this will do the trick.

[Updated on: Tue, 26 December 2017 12:24]

Report message to a moderator

Previous Topic: How To Delete older data less than 10 years
Next Topic: Set timestamp with milliseconds to default to 0 milliseconds
Goto Forum:
  


Current Time: Fri Jan 19 16:37:03 CST 2018

Total time taken to generate the page: 0.01536 seconds