triggers inquiry

Panoy · #1 (**permalink**) 04-02-07, 16:41

hi to all

I recently posted here about triggers support of mySQL and I know want to ask if it is possible in mySQL to process the newly inserted record and pass its value in a certain mySQL user created function or procedure. What I mean is that I would implement a trigger in such a way that it would then process the data of the inserted record and at the same time update that record.

Example, when a certain records is added it the table, then of course my trigger would be executed, and what I would like it to do is to process the data of the newly inserted record and somehow passed that data as an argument in my own function or procedure. Then after processing that newly nserted record will be updated or modified.

I just want to know if it possible in mySQL. I hope that maybe you guys can give me ideas and insight. I'll worry about the coding part. Tnx and god bless.

aschk · #2 (**permalink**) 04-03-07, 07:19

Workflow :

Code:

ATTEMPT INSERT RECORD
|_HIT TRIGGER
   |_AFTER INSERT PERFORM USER PROCEDURE
   |_use (NEW.column) as inputs to procedure

However it occured to me (as i think the other post specified) you don't actually need to use a trigger just :
INSERT INTO <table> (col1,col2) VALUES(user_proc1(val1),val2);
Or something like the above.

Panoy · #3 (**permalink**) 04-03-07, 10:05

i think i need to use the triggers because I will not be modifying the code of the client application, i just need to update and modify the values of that newly inserted record. It will then be viewed by another client application, so in that time, the user can be able to view to modified values.

I have no control of the code that inserts the new record to the database. I think its also a good way that the processing is done in the database. NO need to worry about the code in 2 different client application

Maybe there are also some other ideas. Thanx and god bless

aschk · #4 (**permalink**) 04-03-07, 10:29

I should say that doing it in the SQL queries is a better option, however given that you don't have access to that :

Code:

DELIMITER //
CREATE TRIGGER <trigger name>
  BEFORE INSERT
  ON <table>
  FOR EACH ROW
    BEGIN
      SET NEW.column1 = Function1(NEW.column1);
    END;
//
DELIMITER ;

Internet Services

Web Development

Digital Marketing

Consumer Tech