MySQL Forums
Forum List  »  Triggers

HELP with trigger
Posted by: Stelios Doulakis
Date: March 06, 2012 08:26AM

Hello,
I Have three different database
with names: HLR, Pricing, and amd_smpp_gw.

I need to create an AFTER INSERT trigger into HLR database that make changes
into db:amd_smpp_gw in the table:ported depends on the values
of the db:pricing table: mumbering_mcc_mnc.

A real example:

Step 1:
When i insert a row into table performed in db HLR
i need to check the response field on that table:
i use this code for this:

CODE:
IF (HLR.performed.responce="OK") THEN

STEP 2:
after the check in step 1
i need to cancateneted two fields in the db HLR table performed with field names mcc and mnc.

CODE:
CONCAT(HLR.mcc,HLR.mnc)

STEP 3
After this i need to create this concatenated field with the mcc_mnc field in db pricing - table mumbering_mcc_mnc

CODE:
IF(CONCAT(HLR.mcc,HLR.mnc)<>pricing.mcc_mnc)

STEP 4
I need to check some digits of msisdn field of the HLR db - table performed with the cns field of the db pricing -table numbering_mcc_mnc
but the check of that fields must be the same only of the number of digits that has the cns field (the number of digits is not fixed length) of the pricing db - table numbering_mcc_mnc.
How can i do this?
How can i compare two fields in this way?

After this checks
depends on the results
i have to replace or delete some values in the amd_smpp_gw.


Can you help me please with this?


i have create an ideal trigger can you please help me because is the first time that create a trigger and is very complecated.
Here is the CODE:

CREATE TRIGGER ported
AFTER INSERT ON HLR.performed
FOR EACH ROW
BEGIN
IF (HLR.performed.responce="OK") THEN
IF(CONCAT(HLR.mcc,HLR.mnc)<>pricing.mcc_mnc AND HLR.performed.msisdn==pricing.numbering_mcc_mnc.cns) THEN
REPLACE INTO amd_smpp_gw.ported(msisdn,mcc_mnc) VALUES (HLR.performed.msisdn,CONCAT(HLR.mcc,HLR.mnc));
IF ELSE (CONCAT(HLR.mcc,HLR.mnc)<>pricing.mcc_mnc AND HLR.performed.msisdn==pricing.numbering_mcc_mnc.cns) THEN
DELETE * FROM amd_smpp_gw.ported WHERE amd_smpp_gw.ported==HLR.performed.msisdn
END IF;
END IF;
END;


Kind regards

Options: ReplyQuote


Subject
Views
Written By
Posted
HELP with trigger
2424
March 06, 2012 08:26AM
973
March 07, 2012 12:22PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.