MySQL Forums
Forum List  »  Other Migration

Re: how to convert sql trigger to mysql
Posted by: Peter Brawley
Date: July 16, 2022 07:23AM

In your DDL, KEY `autoid` (`autoid`) is utterly redundant. Lose it.

I overlooked that the "inserted" table referenced in your mssql code is the Sql Server utility table of that name. There is no MySQL equivalent, so your Triggers are even simpler. Try this script ...

drop table if exists coaold;
CREATE TABLE `coaold` (
`autoid` int(11) NOT NULL AUTO_INCREMENT,
`txtid` varchar(45) DEFAULT NULL,
`coaid` varchar(45) DEFAULT NULL,
`accounttitle` mediumtext DEFAULT NULL,
`openingbalance` decimal(18,2) DEFAULT NULL,
`accountcategory` varchar(45) DEFAULT NULL,
`accountgroup` varchar(45) DEFAULT NULL,
`accounttype` varchar(45) DEFAULT NULL,
`entrydate` date DEFAULT NULL,
`FYEAR` varchar(45) DEFAULT NULL,
`username` varchar(45) DEFAULT NULL,
PRIMARY KEY (`autoid`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4;

drop trigger if exists coaold_ins;
create trigger coaold_ins before insert on coaold for each row
set new.accounttype = if( new.openingbalance > 0, 'DR', 'CR' );

drop trigger if exists coaold_upd;
create trigger coaold_upd before update on coaold for each row
set new.accounttype = if( new.openingbalance > 0, 'DR', 'CR' );

-- TEST
insert into coaold set openingbalance=0,entrydate=curdate(),username='pb';
select * from coaold;
update coaold set openingbalance=1;
select * from coaold;

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: how to convert sql trigger to mysql
262
July 16, 2022 07:23AM


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.