MySQL Forums - Triggers

Adding two different Event triggers for the same table (3 replies)

sushaanth srirangapathi — Fri, 26 Apr 2013 03:54:39 +0000

I creates a "Before Insert" and "before Update" trigger on a table.But only one trigger is firing.To be more specific Only the last added trigger is firing. Example : table has three columns (a,B,c) and A is the primary Key column. I have to write trigger which will auto populate A using values in B and C. So i need both "before insert" and "before update" triggers. Can you guys give me help on this. I'm using MySQL workbench.

trigger gets automatically deleted (2 replies)

SK JASIMUL ISLAM — Tue, 26 Mar 2013 14:05:01 +0000

hi,

I have created some trigger(most of before delete) but after few min all the triggers get automatically deleted, any suggestion will be appreciate.
Thanks

trigger wich select field from another DB (no replies)

Francis Goblet — Mon, 18 Mar 2013 21:32:41 +0000

In my after update trigger,a select from another DB return table `db`.`table` doesn't exists. I am sure that the DB and tables exists !

Mysql 5.1
2 DB, DB1 & DB2

when inserting record into a table of DB1, I need a value comming from a table of DB2
so I use this code to ask for value

SET inum = (SELECT num FROM `DB2`.`t1` WHERE indexNum = localIndex);

The problem is that the "DB2.t1 doesn't exists" message.

Tried with or without Quotes is the same

The SELECT perform OK when used outside Trigger

What is the correct syntax for this command ?

Thanks for your help

The delete trigger not firing (1 reply)

Thahir Pattanathel — Tue, 05 Mar 2013 16:21:31 +0000

The delete trigger not firing if deletion of a row initiated by on delete cascade.

Triggers are not working properly after converting code from SQL SERVER (1 reply)

tcee sutton — Tue, 05 Mar 2013 16:28:52 +0000

I'm in the process of migrating Triggers from SQL Server to Mysql. Based on the Table created in SQL SERVER, Some of the columns are NULL until the User inputs info into table. When you run the table in SQL SERVER Actionid is auto-incremented and the rest of the fields do not let you put in erroneous information. However, I had to convert and migrate the SQL SERVER Trigger to MYSQL and although my table doesn't show any errors, when I run the table in MYSQL it is allowing me to put ANYTHING in the field and creating erroneous entries.

Can someone Please look at my code and tell me where I am going wrong. I truly appreciate it.

SQL SERVER TRIGGER:
USE [a7itm]
GO
/****** Object: Trigger [dbo].[tr_act_delete] Script Date: 02/11/2013 06:45:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[tr_act_delete] ON [dbo].[tr_action]
FOR DELETE AS
DECLARE @id INTEGER, @attachments INTEGER
SELECT
@id = deleted.actionid, @attachments = deleted.attachments
FROM deleted
BEGIN
IF @attachments > 0
BEGIN
DELETE FROM tr_link WHERE linkheaderid IN
(SELECT linkheaderid FROM tr_linkheader WHERE parentclasstype='artemispm.trdo.TRAction' AND parentid=@id)
DELETE FROM tr_linkheader WHERE parentclasstype='artemispm.trdo.TRAction' AND parentid=@id
END
DELETE FROM tr_comment WHERE parentclasstype='artemispm.trdo.TRAction' AND parentid=@id
DELETE FROM tr_alert WHERE parentclasstype='artemispm.trdo.TRAction' AND parentid=@id
END
--------------------------------------------------------------
MYSQL TRIGGER:

USE `a7itm`;
/****** Object: Trigger [dbo].[tr_act_delete] Script Date: 02/09/2013 17:12:24 ******/
DROP TRIGGER IF EXISTS tr_act_delete;
DELIMITER $$
CREATE TRIGGER `tr_act_delete` AFTER DELETE ON `tr_action`
FOR EACH ROW BEGIN
DECLARE `id` INTEGER;
DECLARE `attachments` INTEGER;
SET
`id` = deleted.actionid, `attachments` = deleted.attachments;

IF `attachments` > 0 THEN
BEGIN
DELETE FROM tr_link WHERE linkheaderid IN
(SELECT linkheaderid FROM tr_linkheader WHERE parentclasstype='artemispm.trdo.TRAction' AND parentid=`id`);
DELETE FROM tr_linkheader WHERE parentclasstype='artemispm.trdo.TRAction' AND parentid=`id`;
END;
DELETE FROM tr_comment WHERE parentclasstype='artemispm.trdo.TRAction' AND parentid=`id`;
DELETE FROM tr_alert WHERE parentclasstype='artemispm.trdo.TRAction' AND parentid=`id`;
END IF;
END$$
DELIMITER ;

how to update a record (1 reply)

sam sam — Sat, 09 Feb 2013 19:09:50 +0000

Hi when I executed an update statement to update a single record, my trigger update all records with the values of this record.

Here is my trigger:

DELIMITER $$
CREATE TRIGGER UPDATE_PRODUCT_ARTICLE_TRIGGER
AFTER UPDATE ON product_article
FOR EACH ROW BEGIN
update myisam_product_article
SET
product_id = NEW.product_id
AND a_desc = NEW.a_desc
AND header = NEW.header
AND parent_category_name = NEW.parent_category_name
AND sub_category_name = NEW.sub_category_name
AND postcode = NEW.postcode
AND latitude = NEW.latitude
AND longitude = NEW.longitude
AND creation_date = NEW.creation_date
AND suburb = NEW.suburb
AND state = NEW.state
WHERE article_id = NEW.article_id;
END$$
DELIMITER ;

update sql statement used:

update product_article set suburb="BLACKTOWN" where postcode="2148";

How to change my trigger for this case?

Thanks
Sam

mysqldump triggers (1 reply)

R Dydo — Sat, 09 Feb 2013 19:11:06 +0000

I am using mysqldump to do backups which include triggers and it results with the following delimiter syntax errors for the triggers:

ERROR at line 1059: DELIMITER must be followed by a `delimiter` character or string

ERROR 1064 (42000) at line 1060: You have an error with your SQL syntax; check the manual that corresponds to your MySQL server version from the right syntax to use near `` at line 5

Line 1059 is this: DELIMITER ;;
This is setting the delimiter before the trigger statments.

The triggers were put in with DELIMITER $$. However, when backed up the resulting sql file has DELIMITER ;; instead of $$. Is there a way to make mysqldump output the original delimiters used to created the trigger?

Thanks

-r

Dynamically Referencing OLD/NEW values? (4 replies)

Chad Bachmeyer — Sat, 09 Feb 2013 19:06:59 +0000

I am a trigger newbie working on a trigger that will log table changes into a history table in xml format. Basically when a row is updated, the old row gets written into an xml text field which is inserted into history_table.xml.

CREATE TRIGGER history BEFORE UPDATE ON table1
FOR EACH ROW
BEGIN
DECLARE xmloutput TEXT;

SET xmloutput = concat(
"",OLD.field1,"",
"",OLD.field2,""
"",OLD.field3,""
);

insert into `history_table` (`tableName`,`updated_by`,`xml`) values ('table1',NEW.updated_by,xmloutput);
END

To keep from having to write out each field in "table1" (some of these tables can contain over 100 fields) and to keep from having to drop/create a new trigger whenever columns are added/removed from "table1", is it possible to loop through "table1" columns and reference the corresponding OLD/NEW values dynamically?

Any help would be greatly appreciated.
Thank You,
Chad

Database Triggers (1 reply)

Ken Ramkeesoon — Tue, 15 Jan 2013 09:49:06 +0000

**
Have created a trigger that on insert to ONE table triggers an insert to ANOTHER table.

I want to include a value from the first table in a column of the second table.

How do I do this ?

How To Code This Trigger (1 reply)

Chris Thompson — Tue, 15 Jan 2013 09:52:04 +0000

Hi All,

I'm wondering if someone can help me with coding a trigger based on the following code ? I'm a bit rusty with Triggers and this one is especially complicated for me. I've tried to simplify the code a bit with some generic terms. The basic idea of what I want to do is>:

IF (SELECT target_field1 FROM Database1.Table WHERE verificationfield1 = 'data') <>
(SELECT target_field2 FROM Database2.Table WHERE verificationfield2 = 'data')
THEN
UPDATE Database1.Table
SET target_field1 = (SELECT target_field2 FROM Database2.Table WHERE verificationfield2 = 'data')
WHERE verificationfield1 = 'data'
END IF;

I'm looking to fire this trigger AFTER an UPDATE to the Database2.Table field. Any help would be tremendously appreciated.

MYSQL triggers (8 replies)

Mads Kristensen — Fri, 07 Dec 2012 12:50:33 +0000

Hello everybody

First off all thank you for a great forum. I really appreciate all the help i get here.

I have posted this code in another threat, but this question is slighty different. I have to make a trigger. The question sounds like this:
"Create a trigger that removes the season if unused whenever information about when and who produces a product is deleted (i.e., whenever a record is removed from the 'Produces' table, check if there are other records about product being produced for the same season, if not, remove also the corresponding record from the season table)"

the MYSQL code is here:

CREATE TABLE IF NOT EXISTS Dvds(
Serial integer NOT NULL,
Name varchar(50),
Year integer,
Genre varchar(50),
Price integer,
PRIMARY KEY (Serial));

CREATE TABLE IF NOT EXISTS Shops(
Id integer NOT NULL,
Name varchar(50),
Address varchar(50),
PRIMARY KEY (Id));

CREATE TABLE IF NOT EXISTS Customers(
CNo integer NOT NULL,
AccNo integer,
Time varchar(50),
PRIMARY KEY (CNo));

CREATE TABLE IF NOT EXISTS ContactPersons(
Id integer NOT NULL,
Name varchar(50),
Phone integer,
PRIMARY KEY (Id));

CREATE TABLE IF NOT EXISTS Seasons(
StartDate date NOT NULL,
EndDate date NOT NULL,
PRIMARY KEY (StartDate,EndDate));

CREATE TABLE IF NOT EXISTS WebShops(
Id integer NOT NULL,
Url varchar(50),
FOREIGN KEY (Id) REFERENCES Shops (Id),
PRIMARY KEY (Id));

CREATE TABLE IF NOT EXISTS Producer(
Id integer NOT NULL,
Address varchar(50),
Name varchar(50),
PRIMARY KEY (Id));

CREATE TABLE IF NOT EXISTS Sold(
Id integer NOT NULL,
CNo integer NOT NULL,
Serial integer NOT NULL,
FOREIGN KEY (Id) REFERENCES Shops (Id),
FOREIGN KEY (CNo) REFERENCES Customers (CNo),
FOREIGN KEY (Serial) REFERENCES Dvds (Serial),
PRIMARY KEY (Id,CNo,Serial));

CREATE TABLE IF NOT EXISTS Has(
Id integer NOT NULL,
Serial integer NOT NULL,
FOREIGN KEY (Id) REFERENCES Shops (Id),
FOREIGN KEY (Serial) REFERENCES Dvds (Serial),
PRIMARY KEY (Id,Serial));

CREATE TABLE IF NOT EXISTS Has2(
Serial integer NOT NULL,
Producer_Id integer NOT NULL,
StartDate date NOT NULL,
EndDate date NOT NULL,
ContactPersons_Id integer NOT NULL,
FOREIGN KEY (Serial) REFERENCES Dvds (Serial),
FOREIGN KEY ( Producer_Id) REFERENCES Producer (Id),
FOREIGN KEY (StartDate) REFERENCES Seasons (StartDate),
FOREIGN KEY (EndDate) REFERENCES Seasons (EndDate),
FOREIGN KEY (ContactPersons_Id) REFERENCES ContactPersons (Id),
PRIMARY KEY (Serial,Producer_Id,StartDate,EndDate,ContactPersons_Id));

I have a feeling of it is something like this:

CREATE TRIGGER `Seasons_before_delete`
AFTER DELETE ON `Seasons`
FOR EACH ROW
BEGIN
DELETE FROM seasonstart
WHERE seasonstart.???????
DELETE FROM seasonend
WHERE seasonend.??????
END

but i really dont know. My teacher told me that i could use "IF" , ELSEIF, GOTOEND? But am i on the right track? Im really blank right now what to do, so hope someone have a suggestion, what i could do to solve this?

Best Regards
Mads

1442 - Can't update table (1 reply)

Tomasz Adamczyk — Sat, 01 Dec 2012 22:09:48 +0000

I've got this trigger:

CREATE DEFINER = `root`@`localhost` TRIGGER `ograniczenie` BEFORE INSERT ON `wyjscia`
FOR EACH ROW
BEGIN
SELECT COUNT( * ) INTO @cnt FROM wyjscia;
IF @cnt >=5 THEN
DELETE FROM wyjscia WHERE id = ((SELECT MIN(id) FROM wyjscia)) LIMIT 1 ;
END IF ;
END

Error: #1442 - Can't update table 'wyjscia' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Is there any solution of this problem? :(

ERROR 1442 - Cannot update table which has trigger (2 replies)

Daniel Xyz — Sun, 18 Nov 2012 13:26:31 +0000

The problem is like in a title, Ive read some solutions and used "NEW.colum_name" keyword to solve the problem. Trigger definition:

CREATE TRIGGER `trigger1` BEFORE UPDATE ON `driver`
FOR EACH ROW BEGIN
UPDATE driver
SET NEW.is_available=0
WHERE NEW.break_finish_time >= now() && NEW.break_start_time <= now();
END

And when I want to update table 'driver':

ERROR 1442: Can't update table 'driver' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
SQL Statement:
UPDATE `taxi`.`driver` SET `break_finish_time`='2012-11-09 12:13:00' WHERE `iddriver`='2'

'driver' create statement:

================================================================
delimiter $$

CREATE
DEFINER=`root`@`localhost`
TRIGGER `taxi`.`trigger1`
BEFORE UPDATE ON `taxi`.`driver`
FOR EACH ROW
BEGIN
UPDATE driver
SET NEW.is_available=0
WHERE NEW.break_finish_time >= now() && NEW.break_start_time <= now();
END
$$

CREATE TABLE `driver` (
`iddriver` int(10) unsigned NOT NULL AUTO_INCREMENT,
`is_available` tinyint(1) NOT NULL,
`driver_name` varchar(45) COLLATE utf8_polish_ci NOT NULL,
`driver_surname` varchar(45) COLLATE utf8_polish_ci NOT NULL,
`driverpoint_horizontal` double NOT NULL,
`driverpoint_vertical` double NOT NULL,
`break_start_time` timestamp NULL DEFAULT NULL,
`break_finish_time` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`iddriver`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci$$
================================================================

It doesnt have any foreign keys, however other tables does have foreign key which uses 'iddriver' from 'driver' table.

Is there any solution?

Trigger for deleting rows by checking the length of dynamically created table size (no replies)

Preethi K — Mon, 05 Nov 2012 06:27:29 +0000

I have a fixed length table(purchased) which is updated by a dynamically created table(cart).
i want to give a trigger to purchased in such a way that if the purchased table length meets its maximum size it should delete the oldest data in it to let insert the new data.
I'm stuck at the point of how many rows to delete before inserting into the table, because i will be copying the whole contents of cart table which is created dynamically, whose size is unpredictable.
any help would be appreciated

Need Help with TRIGGER and IF (3 replies)

James House — Tue, 23 Oct 2012 21:58:27 +0000

I have a PHP app that inserts 'Pass', 'Fail' or 'NA' into fields TEST1, TEST2, TEST3... through TEST15.

I have a field called OVERALL that needs to be automatically updated with 'FAIL' if any of the TEST1-TEST15 fields contains 'Fail'.

I'm pretty sure I need to use a trigger containing the update statement and either CASE or IF, but I haven't been able to nail down the exact syntax.

Can anyone please help a MySQL noob? I'm using MySQL Server version: 5.1.63-0+squeeze1 (Debian).

Trigger Problem (1 reply)

José Maia Neto — Tue, 23 Oct 2012 17:52:00 +0000

I tryed to create this trigger with PHPMyAdmin and MySQL Server (5.1.54-rel12.6-log).
And PHPMyAdmin tell me a success.
But when i list all the Trigger, there is nothing!
x.X

DELIMITER $$

DROP TRIGGER IF EXISTS auChecarPosicao;
CREATE TRIGGER auChecarPosicao
AFTER UPDATE ON com_orcamento_det
FOR EACH ROW
BEGIN
SET @_result = 0;

IF(OLD.orcd_situacao <> NEW.orcd_situacao AND NEW.orcd_situacao <> 6 AND NEW.orcd_situacao <> 5)
THEN
SELECT orcc_id_posicao INTO @pos
FROM com_orcamento_cab
WHERE orcc_id_orcamento = NEW.orcd_id_orc_cab
AND orcc_id_revisao = NEW.orcd_id_orc_cab_revisao;

IF(@pos = 1)
THEN
IF(NEW.orcd_situacao <> 1 OR NEW.orcd_situacao <> 11)
THEN
SET @_result = 3;
END IF;
ELSE IF (@pos = 17)
THEN
SET @_result = 3;

IF(NEW.orcd_situacao = 17)
THEN
SELECT count(orcd_situacao) INTO @res
FROM com_orcamento_det
WHERE orcd_id_orc_cab = NEW.orcd_id_orc_cab
AND orcd_id_orc_cab_revisao = NEW.orcd_id_orc_cab_revisao
AND orcd_situacao <> 17;

IF(@res = 0)
THEN
SET @_result = 17;
END IF;
END IF;
END IF;
END IF;

IF(@_result <> 0)
THEN
UPDATE com_orcamento_cab
SET orcc_id_posicao = @_result
WHERE orcc_id_orcamento = NEW.orcd_id_orc_cab
AND orcc_id_revisao = NEW.orcd_id_orc_cab_revisao;
END IF;
END $$ DELIMITER ;

after insert trigger problem (no replies)

diego hernandez — Mon, 24 Sep 2012 16:43:34 +0000

hi, i'm new with mysql and i have a problem that i don't undertand what happend.
i'm using mysql workbench 5.2.37
thats what i've done

delimiter [;
create trigger trans_back_up1
after insert on trans_tabla1
for each row
begin
delete from trans_tabla_inter;
insert into trans_tabla_inter(nombre_tabla) values ('trans_tabla1');
end;
[;

delimiter [;
create trigger trans_back_up2
after insert on trans_tabla_inter
for each row
begin
insert into trans_tabla_back_up
select *
from trans_tabla1;
end;
[;

the problem is that when i insert into trans_tabla1, 5 rows from a file, i have the 5 rows fine on trans_tabla1 but on the table trans_tabla_back_up i have duplicates, i have 5 copies of the first row, 4 copies of the second, 3 of the third, 2 of the fourth , and 1 of the fifth.

can someone tell me where is the error

Trigger (1 reply)

Jijgee Jamsran — Tue, 25 Sep 2012 06:50:21 +0000

Hello, i have problem when i create trigger. I have a table name "request". and i want to check record when new record is inserted. The "request" table has a column named "number". if record has negative value for number column, i want to dismiss the insert statement.
Help me.

Want to create trigger on mysql 5.0.45 with IF NOT EXISTS (2 replies)

Prashesh Kachhia — Tue, 25 Sep 2012 06:05:48 +0000

I have tried to create trigger on mysql 5.0.45 but it will give error.Same trigger I have successfully created on mysql 5.1.61.

My requirement is, I have two databases and I want to insert into one database table after insert of other database table and also vice a verse.

My trigger is

DELIMITER $$
CREATE DEFINER=root@localhost TRIGGER trigeer_with_if AFTER insert ON `test_new`.employees
FOR EACH ROW
Begin
	If NOT EXISTS(SELECT employeeNumber FROM `test_db`. employees WHERE employeeNumber = NEW.employeeNumber) THEN
	INSERT INTO `test_db`. employees VALUES (NEW.employeeNumber,NEW.lastName,NEW.firstName,substr(NEW.extension,0,LENGTH(NEW.extension)-2),NEW.email,NEW.officeCode,NEW.reportsTo,NEW.jobTitle);
	end if;

END$$
DELIMITER ;

But I have tried to create this trigger though phpmyadmin on mysql 5.0.45 it will gives me below error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$
CREATE DEFINER=root@localhost TRIGGER trigeer_with_if AFTER insert' at line 1

I am new to trigger, so don't know how to resolve this problem.

Thanks,
Pkachhia

Creating a payroll profile out of a new employee record (no replies)

Jack Luk — Sat, 22 Sep 2012 09:22:15 +0000

Structure:
employee - employee_id, employee_name, pin_no
payroll - employee_id, pay_period, gross_income

-- Trigger DDL Statements
DELIMITER $$

USE `edica`$$
CREATE TRIGGER CreatePayrollProfile
   AFTER INSERT ON employee
   FOR EACH STATEMENT
     INSERT INTO payroll
         VALUES (employee.employee_id,
                 '2012-2-30',
                 '0.00');

There is an error on FOR EACH STATEMENT. Just a red cross on the left.
And If I want to trace the pay_period, do I open up another table containing this info driven by the user?
Thanks
Jack

trigger Throw error message (1 reply)

natukunda liz — Tue, 18 Sep 2012 06:41:10 +0000

i need a trigger to give an error message if invalid data is inserted.here is my trial and its not working. please help on error handling in mysql. thank you.

mysql> DELIMITER //
mysql> CREATE TRIGGER TR_CHECKCONSTRAINT
-> BEFORE INSERT ON TEST_CHECKCO
-> FOR EACH ROW
-> BEGIN
-> DECLARE msg varchar(255);
-> IF NOT new.AGE = 50 THEN
-> SET msg = 'INVALID DATA'
-> SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
-> END IF;
-> END
-> //

MySQL Trigger performance (no replies)

Venkat Ram Santosh — Wed, 12 Sep 2012 18:44:20 +0000

I am relatively new to MySQL and worked on Oracle previously.

I wanted to know if anyone worked significantly with triggers on MySQL and have any benchmarks for its performance . Have anyone had experience where triggers made the inserts, updates, deletes very slow, becoming a bottle neck or bringing down the server?

Can't create the trigger i always get an error. (1 reply)

Jorge Diaz — Wed, 12 Sep 2012 20:17:09 +0000

Hello guys, Im new here and i have never used triggers in Mysql, this is the first time, and im having problems migrating one trigger from sql server to mysql, i always get an error when i try to create it, this is the trigger im trying to create in mysql:

create trigger insertarAccesos after insert on perfil
for each row begin
declare @Perfil varchar(30),
set @Perfil = (Select idPerfil from perfil),
insert into Acceso values(@Perfil,0,0,0,0,0,0,0)
end;

And it says i have some problems with te sintax, can anyone help me please? thanks in advance.

Problem with Trigger (2 replies)

Reinhard Windpassinger — Wed, 12 Sep 2012 20:21:28 +0000

Hello,

i have a little problem with my trigger.
i created a trigger:

BEGIN

IF((SELECT `Wert` FROM parameter_history WHERE `BusNr` = NEW.BusNr AND `MesswertNr` = NEW.MesswertNr ORDER BY `Timestamp` DESC LIMIT 1) != NEW.Value)
THEN
INSERT INTO parameter_history (`BusNr`, `MesswertNr`, `Value`) VALUES (NEW.BusNr, NEW.MesswertNr, NEW.Value);
END IF;

END

The event of the trigger is after an update.
if there is already a "New.Value" it works really good.
but if there is no "New.Value" in my database it doesn`t work and i don`t know why.
The problem is, that i can`t get a value back from "NEW.Value" when there is actually no entry in my table.

Can anybody help me?

kind regards

syntax (1 reply)

Marco Chan — Fri, 31 Aug 2012 12:08:14 +0000

Hi
what wong's with this syntax?
It hightlight the last 2 lines not correct, I wonder if this is the ";" ...
Thanks

CREATE TRIGGER UPD_INTEGRAL_NO BEFORE INSERT ON mt4.mt4_trades
FOR EACH ROW
IF INSTR(BINARY new.comment,'O:') >0 THEN
SET NEW.INTEGRAL_OPEN = MID(new.comment,INSTR(BINARY new.comment,'O:')+2,8);
END IF;

Is it possible to delete record after selecting it? (1 reply)

Yazaki NA — Wed, 29 Aug 2012 07:28:36 +0000

Hello. Is it possible to create trigger which would delete record after selecting that record with SELECT * ... query?

collision with auto_increment & triggers across threads (no replies)

Chris Cheshire — Fri, 24 Aug 2012 02:27:27 +0000

DB : MySQL 5.5.23, InnoDB (Amazon RDS installation)

I Have a DB with separate tables for payments, credits and charges, with a master transaction table to keep them all in correct order between them all. This was recently moved from Oracle where I used one sequence to generate the transaction ids across all the payments, credits, charges tables.

For MySQL, I have had to change this around since auto_increments work slightly differently.

I have an auto_increment field on my transaction master table, and a trigger to generate the transaction ID based upon that on a before insert. These transaction ids are then the PK in the charge, credit & payment tables.

+----------------+---------------------+------+-----+---------+----------------+
| Field          | Type                | Null | Key | Default | Extra          |
+----------------+---------------------+------+-----+---------+----------------+
| iid            | bigint(20)          | NO   | PRI | NULL    | auto_increment |
| member_id      | varchar(20)         | NO   | MUL | NULL    |                |
| transaction_id | varchar(20)         | NO   |     | NULL    |                |
| timestamp      | datetime            | NO   | PRI | NULL    |                |
| currency       | tinyint(3) unsigned | NO   |     | NULL    |                |
+----------------+---------------------+------+-----+---------+----------------+

(the timestamp field is part of the primary key as it is partitioned by date)

create trigger trg_i_usa_transactions
before insert on usa_transactions
for each row
begin
  if length(new.transaction_id) = 4 then
    set new.transaction_id = (
        select concat(new.transaction_id, lpad(cast(auto_increment as char), 12, '0'))
        from information_schema.tables
        where table_schema = database() and table_name = 'usa_transactions'
    );
  end if;
end$$

Now, I have stored procedures for creating the payments, charges and credits that, after some sanity and business rule checks, generates an id via :

        insert into usa_transactions (
            member_id, transaction_id, timestamp, currency
        )
        values (
            memberid, 'USAG', timestamp, 1
        );

        select t.transaction_id 
        into chargeid
        from usa_transactions t
        where t.iid = last_insert_id();

Each transaction type has their own prefix (legacy system, that's how it was designed, not important etc).

So, theoretically, for every row of the usa_transactions table, the generated transaction id should have the auto_increment value as part of it.

Unfortunately, that is not happening if 2 different threads try to do an insert into the usa_transactions table at the same time.

+----------+---------------+------------------+---------------------+----------+
| iid      | member_id     | transaction_id   | timestamp           | currency |
+----------+---------------+------------------+---------------------+----------+
| 97988555 | USAC000054506 | USAG000097988555 | 2012-08-24 00:53:00 |        1 |
| 97988556 | USAC000054506 | USAD000097988556 | 2012-08-24 00:53:00 |        0 |
| 97988557 | USAC000057181 | USAG000097988557 | 2012-08-24 00:53:03 |        1 |
| 97988558 | USAC000012043 | USAD000097988557 | 2012-08-24 00:53:05 |        1 |
| 97988559 | USAC000012043 | USAG000097988559 | 2012-08-24 00:53:05 |        1 |
| 97988560 | USAC000057181 | USAD000097988559 | 2012-08-24 00:53:03 |        0 |
+----------+---------------+------------------+---------------------+----------+

Rows 1, 2, 3, 5 are correct, Row 4 has the transaction_id based off the previously inserted row. Row 6 has similar behaviour.

The transactions were being created by two different processes on the same machine, neither sharing connections. One was a single-threaded standalone java app, the second was running under tomcat. Both have autocommit turned off.

I have tested this quite a bit. When only one thread from anywhere is creating the transactions, they line up from iid --> transaction_id. When another thread using a completely new connection starts to create its own transactions at the same time, that's when the problems occur. I was even able to duplicate it by doing the same inserts via a mysql client and bypassing the stored procedures.

Is there something wrong with the trigger code or the way I am getting the generated record in the stored procedure? Is it a bug in this version of the DB engine?

Help

Thanks

Chris

Tigger to Search String (4 replies)

Pedro Podclan — Thu, 30 Aug 2012 11:47:46 +0000

Hello, im need help us. I have one field in database than have some data ( string). Under this string i have some integers. Its possible take just this integes and insert in other table? Ty for all

Trigger is not working (1 reply)

Marco Antonio Rios — Tue, 21 Aug 2012 04:57:57 +0000

hello,
I am creating a table and a TRIGGER so when I create/update a register, it calculates by itself.
If I left "blank" the column that is to be calculated, I have an error "1064"

------------------------------------------------------------
delimiter $$

CREATE TABLE res_pro_servicios (
id_servicio int(10) unsigned NOT NULL AUTO_INCREMENT,
id_proveedor_res int(10) unsigned DEFAULT NULL,
no_reservacion int(11) DEFAULT NULL,
servicio varchar(255) DEFAULT NULL,
descripcion varchar(255) DEFAULT NULL,
fecha date DEFAULT NULL,
subtotal decimal(10,2) DEFAULT '0.00',
iva decimal(10,2) DEFAULT '0.00',
total decimal(10,2) DEFAULT '0.00',
confirmar_cv01 set('si','no') NOT NULL DEFAULT 'no',
cancelado_cv01 set('si','no') NOT NULL DEFAULT 'no',
eliminado_hi01 set('si','no') NOT NULL DEFAULT 'no',
PRIMARY KEY (id_servicio)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci$$

CREATE TRIGGER calcula_iva_res_servicios
BEFORE UPDATE ON res_pro_servicios
FOR EACH ROW
BEGIN
UPDATE res_pro_servicios
SET total = (NEW.subtotal*(NEW.iva/100))
WHERE id_servicio = NEW.id_servicio;
END$$

DELIMITER ;
------------------------------------------------------------

Trigger to message an application (1 reply)

patrick schomburg — Tue, 21 Aug 2012 04:59:51 +0000

I know this probably comes up now and again, and i've searched and searched. What I need to do is fire an event or send a message on inserts/updates/deletes for specific tables that will tell applications using the database to reload the data they have displayed. I am using c# for the applications, and data is also inserted via php.

There must be some common solution out there for this that I just don't see.

Technically, the application on the server only needs to see the message, then it could message the other systems on the LAN.