CODEWITHSUNDEEP
sqltriggers
Abhishek
Abhishek

Reputation: 55

multithreading with the trigger

I have written a Trigger which is transferring a record from a table members_new to members_old. The Function of trigger is to insert a record into members_old on after insert in members_new. So suppose a record is getting inserted into a members_new like 

nMmbID   nMmbName  nMmbAdd 

1        Abhi     Bangalore

This record will get inserted into members_old with the same data structure of the table

My trigger is like : 

create trigger add_new_record 
after 
insert on members_new
for each row 
INSERT INTO `test`.`members_old`
(
`nMmbID`, 
`nMmbName`,
`nMmbAdd`
)
(
SELECT
`members_new`.`nMmbID`, 
`members_new`.`nMmbName`,
`members_new`.`nMmbAdd`
FROM `test`.`members_new`
where nMmbID = (select max(nMmbID) from `test`.`members_new` // written to read   the last record from the members_new and stop duplication on the members_old , also this will reduce the chances of any error . ) 
)

This trigger is working for now , but my confusion is that what will happen if a multiple insertion is happening at one instance of time.

Will it reduce the performance?

Will I face deadlock condition ever in any case as my members_old have FKs?

If any better solution for this situation is there, please give limelight on that

Upvotes: 0

Views: 169

Answers (1)

fancyPants
fancyPants

Reputation: 51888

From the manual:

You can refer to columns in the subject table (the table associated with the trigger) by using the aliases OLD and NEW. OLD.col_name refers to a column of an existing row before it is updated or deleted. NEW.col_name refers to the column of a new row to be inserted or an existing row after it is updated. 

create trigger add_new_record 
after 
insert on members_new
for each row 
INSERT INTO `test`.`members_old`
SET
`nMmbID` = NEW.nMmbID,
`nMmbName` = NEW.nMmbName,
`nMmbAdd` = NEW.nMmbAdd;

And you will have no problem with deadlocks or whatever. Also it should be much faster, because you don't have to read the max value before (which is also unsecure and might lead to compromised data). Read about isolation levels and transactions if you're interested why...

Upvotes: 1

Related Questions