TYPE=TRIGGERS
triggers='CREATE DEFINER=`root`@`localhost` TRIGGER `before_insert_upi_transactions`
\nBEFORE INSERT ON `upi_transactions`
\nFOR EACH ROW
\nBEGIN
\n    -- Set pacs_acc_no before inserting
\n    DECLARE v_acc_no VARCHAR(50);
\n
\n    -- Get acc_no from deposit_Acc based on the condition
\n    SELECT `acc_no` 
\n    INTO v_acc_no
\n    FROM `deposit_Acc` 
\n    WHERE RIGHT(`acc_no`, 9) = TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(NEW.`remarks`, \'trnsf\', -1), \'/\', 1))
\n    LIMIT 1;
\n
\n    -- Update NEW.pacs_acc_no with the found acc_no
\n    IF v_acc_no IS NOT NULL THEN
\n        SET NEW.`pacs_acc_no` = v_acc_no;
\n    END IF;
\nEND' 'CREATE DEFINER=`root`@`localhost` TRIGGER `after_insert_upi_transactions`
\nAFTER INSERT ON `upi_transactions`
\nFOR EACH ROW
\nBEGIN
\n    -- Insert into upi_transactions_final with status \'Pending\'
\n    INSERT IGNORE INTO `upi_transactions_final`(`id`, `tran_id`, `value_date`, `transaction_date`, `posted_date`, `ref_no`, `remarks`, `withdraw_amt`, `deposit_amt`, `balance`, `account_no`, `pacs_acc_no`, `status`)
\n    VALUES (NEW.`id`, NEW.`tran_id`, NEW.`value_date`, NEW.`transaction_date`, NEW.`posted_date`, NEW.`ref_no`, NEW.`remarks`, NEW.`withdraw_amt`, NEW.`deposit_amt`, NEW.`balance`, NEW.`account_no`, NEW.`pacs_acc_no`, \'Pending\');
\n
\n    -- Delete the record from upi_transactions after inserting into upi_transactions_final
\n    DELETE FROM `upi_transactions` WHERE `id` = NEW.`id`;
\nEND'
sql_modes=1073741824 1073741824
definers='root@localhost' 'root@localhost'
client_cs_names='utf8' 'utf8'
connection_cl_names='utf8_general_ci' 'utf8_general_ci'
db_cl_names='latin1_swedish_ci' 'latin1_swedish_ci'
