CODEWITHSUNDEEP
mysqltriggers
pmcs management
pmcs management

Reputation: 45

Using a trigger BEFORE UPDATE to INSERT a row into my second table

I have this as table structure for my pension_accounts table and below the structure for my second table pension_statement table to select from table pension_accounts AFTER UPDATE trigger 

Table structure for table `pension_accounts` 

CREATE TABLE IF NOT EXISTS `pension_accounts` (
  `User_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Title` varchar(500) NOT NULL,
  `Surname` varchar(500) NOT NULL,
  `Firstname` varchar(500) NOT NULL,
  `Other_names` varchar(500) NOT NULL,
  `DOB` varchar(500) NOT NULL,
  `Gender` varchar(500) NOT NULL,
  `MaritalStatus` varchar(500) NOT NULL,
  `City` varchar(500) NOT NULL,
  `State` varchar(500) NOT NULL,
  `Nationality` varchar(500) NOT NULL,
  `Country` varchar(500) NOT NULL,
  `Email` varchar(500) NOT NULL,
  `Mobile` varchar(500) NOT NULL,
  `Address` varchar(500) NOT NULL,
  `AccountType` varchar(500) NOT NULL,
  `PASSWORD` varchar(500) NOT NULL,
  `ACCOUNT_NUMBER` varchar(500) NOT NULL DEFAULT '',
  `CountryCode` varchar(500) NOT NULL,
  PRIMARY KEY (`User_id`),
  UNIQUE KEY `Surname` (`Surname`,`Firstname`,`PASSWORD`),
  UNIQUE KEY `Email` (`Email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

DROP TRIGGER IF EXISTS `before_update_pension_accounts`;
DELIMITER //
CREATE TRIGGER `before_update_pension_accounts` BEFORE UPDATE ON `pension_accounts`
 FOR EACH ROW SET new.Modified_DateTime = CURRENT_TIMESTAMP
//
DELIMITER ;

AND SECOND TABLE `

Table structure for table pension_statement

CREATE TABLE IF NOT EXISTS `statement` (
  `StatementID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Statement_Surname` varchar(500) NOT NULL,
  `Statement_Firstname` varchar(500) NOT NULL,
  `Statement_ACCOUNT_NUMBER` varchar(500) NOT NULL,
  `Statement_Balance` varchar(500) NOT NULL,
  `Statement_Modified_DateTime` varchar(500) NOT NULL,
  `Statement_Description` varchar(500) NOT NULL,
  `Statement_CurrencyType` varchar(500) NOT NULL,
  PRIMARY KEY (`StatementID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

PROBLEM: I tried adding an INSERT statement to that trigger to insert a row into pension_statement but it failed;

DROP TRIGGER IF EXISTS `statement_history`;
DELIMITER //
CREATE TRIGGER `statement_history` AFTER UPDATE ON `pension_accounts`
 FOR EACH ROW INSERT INTO `pension_statement` (`StatementID`, `Statement_Surname`, `Statement_Firstname`, `Statement_ACCOUNT_NUMBER`, `Statement_Balance`, `Statement_Modified_DateTime`) VALUES (NULL, 'new.Statement_Surname', 'new.Statement_Firstname', 'new.Statement_ACCOUNT_NUMBER', 'new.Statement_Balance', 'new.Statement_Modified_DateTime')
//
DELIMITER ;

IT RETURNS THIS AS VALUES:

'new.Statement_Surname', 'new.Statement_Firstname', 'new.Statement_ACCOUNT_NUMBER', 'new.Statement_Balance', 'new.Statement_Modified_DateTime'

Upvotes: 0

Views: 121

Answers (1)

pala_
pala_

Reputation: 9010

You can execute more than one statement in a trigger, by enclosing it in a begin ... end block. eg.

DROP TRIGGER IF EXISTS `before_update_pension_accounts`;
DELIMITER //
CREATE TRIGGER `before_update_pension_accounts` BEFORE UPDATE ON `pension_accounts`
 FOR EACH ROW 
 BEGIN
   SET new.Modified_DateTime = CURRENT_TIMESTAMP;
   ... another statement here ... ;
   ... and another one ... ;
 END
//
DELIMITER ;

So simply add your insert statement after the set statement, and make sure it is inside the begin and end.

Okay, now that we have some more information, it is clear that what you want is an after insert trigger. It should look like this:

CREATE TRIGGER `before_update_pension_accounts` after insert ON `pension_accounts`
FOR EACH ROW 
BEGIN
   insert into `statement` (statement_surname, statement_firstname, statement_account_number, statement_balance, statement_modified_datetime, statement_description, statement_currencytype) values(new.surname, new.firstname, new.account_number, 0, now(), 'something', 'something');
END//

You can only use the new prefix to refer to fields from the pension_accounts table in this instance, which means statement_balance, statement_description, statement_currencytype must get set to some default value. statement_modified_datetime can be set to the current time.

here is a demo showing this process working.

I am assuming, since there was no description of what you're trying to accomplish, is that you want to setup a statement, after a new account is opened? If this isn't the case, please go back to the question and describe what is you are trying to accomplish, rather than just showing us what you are trying.

Upvotes: 1

Related Questions