MySQL Forums - Triggers

Execute external script/program (no replies)

chuck lidderdale — Fri, 24 Jan 2025 15:18:55 +0000

OK, I cry UNCLE

I have a need to execute an bash script or a php program when an insert of update to a table happens. The web says create a trigger "sys_exec ...". The web says no longer supported, else where see github server-11 which I can't get to compile. Has the work been embedded in Mariad? And please - detail.

thank you

deadlock happens (no replies)

jun jun — Tue, 05 Dec 2023 09:16:42 +0000

I execute as the following in my code then deadlock happens. Do you know why deadlock happen and have any solutions to resolve ?
begin transaction;
loop {
insert data into A table;
insert data into B table; (get id from a table to insert and some another values)
update data into A table by id.
}
commit;

deadlock happens (no replies)

jun jun — Tue, 05 Dec 2023 09:09:21 +0000

I execute as the following in my code then deadlock happens. Do you know why deadlock happen and have any solutions to resolve ?

loop {
insert data into A table;
insert data into B table; (get id from a table to insert and some another values)
update data into A table by id.

}

Trigger with case possible? (no replies)

erwin traas — Sat, 09 Sep 2023 14:23:43 +0000

Hello,

I'm rather new to triggers, here is what I want to do 'after update' on a certain table.

I want to check the value of a field like :

progress bigger the 0 but less then 100 -> insert a log into a second table
progress equals 100 -> insert another record into the second table

Can this be done by triggers?

Regards
Erwin

Insufficient checks for trigger (no replies)

Huaxiong Song — Tue, 03 Jan 2023 04:57:37 +0000

Sometimes the trigger can be confusing. For example:

1. Trigger can be created successfully to handle an non-existing tables(t2), and it's definer can be an no-existing user(user_1), just like:

DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`user_1`@`%`*/ /*!50003 TRIGGER `trigger_1` AFTER UPDATE ON `t1` FOR EACH ROW BEGIN
INSERT INTO t2 VALUES(new.id);
END */;;

As a result, when update on t1, errors will happen. In my opinion, this trigger should not be successfully created in this case. Errors should be found in the create phase instead of the use phase.

2. Trigger can be created successfully to handle an non-existing column(col_1), just like:

DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`user`@`%`*/ /*!50003 TRIGGER `trigger_2` AFTER UPDATE ON `t1` FOR EACH ROW BEGIN
select col_1 from t3;
END */;;

If t1 has the field col_1, but t3 does not, and user don't know or forget trigger2, then the error report will be quite confusing. The error message is "Unknown column 'col_1' in 'field list'", but it is insuffient, because we don't know "col_1" is in which table. As a user, I will first check table "t1" instead of trigger "trigger_2"(table "t3").

This might be by design, but in some cases, it can be really confusing.

Mysql How to Sync two Database Tables which are on Different Machines Every Few Minutes (1 reply)

Frank Moori — Wed, 13 Jul 2022 19:03:50 +0000

In MySQL how can I sync two Database Tables which are on Different Machines Every Few Minutes?
Please give me any examples and steps

How to check if database is up before my Trigger fires? (2 replies)

Frank Moori — Wed, 24 May 2023 07:21:40 +0000

Hello every one,
I'm wondering if there is a way to check if database is up before my Trigger fires?
and how my trigger can resume from where it left after the DB server is back on and running?
Thanks

I need help with if statement in my trigger statement (1 reply)

Eric Ma — Tue, 28 Jun 2022 13:15:01 +0000

create trigger idcheck after insert on customers
for each row
if (select exists(select * from customers where `Last Name`="Ma"))
then
rollback;
end if;

this is my query. can you tell me what I'm doing wrong with my if statement?

Error 1442 / trigger failing / due to select in insert statement (1 reply)

Stewart Bourke — Thu, 28 Apr 2022 15:34:22 +0000

First, apologies for cross-posting - Posted to the wrong forum initially:

Good evening.

I am aware of the cause of error 1442 in relation to not being able to update a row in a table being called from the trigger in general, but I cannot understand why a trigger fails if the insert statement contains only a select from the table in question

Create trigger t1 in rating
for each row
begin
update title set f1 = new.vote where title.id = new.title_id
end

My insert is:

insert into rating (title_id, vote) values (select id from title where title_name = 'FRED1',25);

The issue is because I am selecting a value from the title table, I cannot update it in the body of the trigger. Surely if it is only selecting then it does not have the potential to cause an infinite-loop, which is what I understand to be the reason why this is not allowed?

Having searched around, I cannot find how to get around this? Are there any 'standard' techniques that people are aware of?

Thank you

Stewart Bourke

After Update Trigger if field = 1 (1 reply)

Poco Poco — Fri, 22 Apr 2022 22:29:27 +0000

Needs Answer MySQL
1
Hi

I am hoping someone can help me out with a project I need to hand up. I need to design a basic DB with tables, data, queries, views, triggers etc. I am trying to create a trigger but it isn't working.

I have a transaction table called tblLoan and wanted an after update trigger on this table only if the late column = 1. The idea is the trigger would write the LoanID and MemebrID from tblLoan table into the tblLate table. My code is below, and I have tried different permutations of this.

SQL
delimiter //
CREATE TRIGGER Update_tblLoan_new
AFTER UPDATE
ON tblLoan FOR EACH ROW
BEGIN
IF (late = 1)
THEN
INSERT INTO tblLate (LoanID, MemberID ) VALUES (LoanID,MemberID);
END IF;
END; //

Error I get is below. Not 100% but it looke like my code it trying toSET the late value into my tblLoan table.

ERROR 1054: 1054: Unknown column 'late' in 'field list'
SQL Statement:

UPDATE `library`.`tblloan` SET `Late` = '1' WHERE (`LoanID` = '1')

Thanks in advance

Poco

Trigger on date correction (1 reply)

Святослав Тарадай — Fri, 01 Apr 2022 16:19:34 +0000

I need to make a trigger on inserting,updating and deleting a row in a table and it must check the correction of the date in a row. the question is: what can i do to make a check on writing date correctly?

ERROR: Unknown column in WHERE clause (7 replies)

Lester Cutchall — Wed, 05 Jan 2022 18:00:56 +0000

Here is the trigger for the Song_Entry table after insert:

UPDATE Contest
SET Songs_Entered =
(Songs_Entered + 1)
WHERE Contest.Contest_id =
Song_Entry.Contest_id;

I keep getting the above error message that says Song_Entry.Contest_id is unknown.

Using tablename in trigger from system (1 reply)

Wolfgang Ritter — Thu, 30 Dec 2021 15:18:47 +0000

Hi

I am using a trigger to audit the transaction on my tables.

e.g. on my user table after an update i want to have following information, as my example shows.

CREATE DEFINER = CURRENT_USER TRIGGER `test`.`dbuser_AFTER_UPDATE`
AFTER UPDATE ON `dbuser`
FOR EACH ROW

BEGIN

INSERT INTO test_audit
SET type_of_action = 'u',
tablename = 'test.dbuser',
userid = new.userid,
ip4 = null,
ip6 = null,
longitude = null,
latitude = null,
time_stamp = NOW();

END

I want to use this trigger on all my tables and i would prefer to use system variable for the table name. Until now, i didn't found the name of the variable to replace my 'test.dbuser' with some kind of a system variable.

The only information i need is the name of the table, where my trigger is connected.

Does someone know the name or where i will find it.

Thanks
Wolfgang

MySQL to trigger update statement on a specific day & time (3 replies)

Bernard Bailey — Tue, 20 Jul 2021 13:56:42 +0000

Hi team,

I have a requirement in MySQL to trigger an update statement on a calendar table on a specific day & time.

I understand triggers can only work on the basis of a BEFORE or AFTER state during an update of a field in a table.

I can not find anywhere for a method to cause a table update, unless I use a cron job.

Are there any MySQL tools that can run a sql statement on the specific day of the week and time?

Or is there a work around which will achieve the same result?

All assistance is appreciated.

Cheers
Bernard Bailey

How to create a before update trigger for 2 fields "expected_hash" and "identify_hash" (both allow null) (5 replies)

Jan Marti — Fri, 02 Jul 2021 13:51:36 +0000

Hi there

If identify_hash != expected_hash, the trigger should deny the update.

I tried EVERYTHING, but there is not solution so it seems...

Major question: Is that possible with MySQL?

I get A LOT of strange error messages like "table does not exist in field list"

Current code is that:

SET SQL_NOTES = 0;

USE pf_server;

DROP TRIGGER IF EXISTS expected_hash_trigger;

DELIMITER $$
CREATE TRIGGER expected_hash_trigger
BEFORE UPDATE ON clientdb_mapping
FOR EACH ROW

BEGIN

IF (new.identify_hash = clientdb_mapping.expected_hash) THEN
-- UPDATE clientdb_mapping SET identify_hash = clientdb_mapping.expected_hash;
DELETE FROM pi_hash_migration_mapping;
END IF;

END $$

DELIMITER ;

SET SQL_NOTES = 1;

Help with a trigger to change field to a negative value (2 replies)

mark paterson — Tue, 11 May 2021 21:46:28 +0000

I have a database called check_register that has one table called transactions. The transactions table has the following columns:

transID int (auto increment) primary key
amount int
withdraw varchar [values are either yes or no]

is there a way to write a trigger that will look to see if the withdraw field = yes then change the amount value to a negative value?

for example, a record entered with the following values:

transID: 1
amount: 20.00
withdraw: yes

the trigger would replace 20.00 with -20.00

Thank you in advance. I'm trying to teach myself ways to have the database help with data manipulation,

Why isn't this working? (6 replies)

John Frank — Wed, 14 Apr 2021 22:06:52 +0000

Hey Everyone,
I am working on a database, and I want to set up a trigger. Here is the last outline of the two tables

Lighting:
|Index | col1 | col2 | Last Updated|
------------------------------------
|1     |info  |info. | 1618335050  |


Lighting Schedule:
|Index | time  | strength |
---------------------------
|1     |12:00  |0         |
---------------------------
|2     |13:00. |20        |
---------------------------
|3     |13:15. |25        |
---------------------------
|2     |13:40. |50        |

I have a python program that will delete all the rows in `Lighting Schedule` and replace insert new information. What I am looking for is a trigger that put a timestamp into row one column "Last Updated" in the `Lighting` table when that happens

The code below keeps shooting back a generic error.

Any ideas?

DELIMITER $$
CREATE TRIGGER 'LightingLastUpdated'
AFTER INSERT
ON `Lighting Schedule`
BEGIN
	UPDATE `Lighting` SET `Last Updated` = TIMESTAMP WHERE 'Index' = 1;
END$$

DELIMITER ;

Trigger :Add an old value with the new value (1 reply)

theo robert — Thu, 21 Jan 2021 17:05:24 +0000

I'm trying to create an trigger on phpmyadmin. My objective is to create a trigger that add the old value of a row with the new value (inserted with a form).

It's for managing the student dues, so if a student already paid $100, and pay $150 more the other time, the trigger have to add the old value ($100) with the new received value ($150), and the field 'already_paid' in the db is now $250.

I've tried something simple like this, but it didn't worked.

(TRIED WITH BEFORE UPDATE )

SET @resultat = already_paid + new.already_paid
UPDATE student_dues
SET already_paid = @resultat
WHERE student_due_id = new.student_due_id

&

UPDATE student_dues
SET versement = versement + new.versement
WHERE student_due_id = new.student_due_id

Debug audit trigger design (3 replies)

Nicola Farina — Fri, 08 Jan 2021 15:04:51 +0000

Hello

I am an experienced pl/sql developer, now attempting to write a trigger in MySql 5.6 (on AWS) to debug a nasty problem.
In our system there is a table in which a couple of fields are updated to a non-legal value, but we do not understand which process is doing it (we have quite a few of them).
Putting a constraint on the field is not an option (it is a legacy system).
We would like to find out _which_ process is doing this faulty update.
In Oracle I would have written a before update trigger which would
1) collect information on the calling session (program_name, client etc) from the v$session table,
2) insert a record in a audit table, and finally
3) raise an exception to block the undesired update.

The only thing I was able to achieve was the 3) (raise an exception with signal state '45000'), but inserting into the audit table at the same time is not possible since MySql lacks the concept of autonomous transaction right?
I am looking for an advice, hoping the use case is clear.
What a experienced MySql admin/dev would do in this case?
Thanks a lot
Bye
Nicola

Would a function or procedure work in my case? Executed by a trigger? (3 replies)

Jim Schwartz — Mon, 04 Jan 2021 16:30:52 +0000

I have six tables:

SALES
S000
S001
S002
S003
S004

I want to create a trigger so that, when a new record is inserted to any of the tables S000...S004, the field SALES.LAST_DATE in a specific record is updated with the value of the field "SALE_DATE" that was just inserted (in S000...s004).

Here is pseudo-code:

---------------------
if table(S000...S004) has a new record inserted then
x = tablename that was just updated
y = SALE_DATE from record that was just inserted
update SALES set LAST_DATE = y where TNAME = x
end if
---------------------

"TNAME" is a field in SALES that holds the tablename (S000...S004)

I've been working with MySQL for a while, but only simple tables and queries. I've never used triggers, functions or procedures.

Is this workable with a trigger, function or procedure?

BTW, I cannot change the design of the tables, because some old programs rely on the table structure just the way it is.

Any advice, suggestions or insights is appreciated in advance! Or even just pointing me in the right direction.

Thanks!

Trigger : AFTER INSERT UPDATE on other table (2 replies)

MICHAEL FAURE — Thu, 12 Nov 2020 17:23:06 +0000

Hi,

I have 2 tables :

1) e_ with :

id_e (INT AI PRIMARY KEY)
c1 (VARCHAR)
c2 (INT)

2) e_h with :

id_e_h (INT AI PRIMARY KEY)
d1 (VARCHAR)
d2 (INT)

------------------------------------------------------------------------------------------

Using a trigger on e_h AFTER INSERT I'd like to UPDATE c2 WHERE c1 = d1

But I get the ERROR subquery returns more than 1 row

------------------------------------------------------------------------------------------

This is the trigger :

UPDATE e_ SET c2 =
(SELECT d2 FROM e_h WHERE c1 = d1)

------------------------------------------------------------------------------------------

May someone help me on how to fix that issue ?

trigger with dynamic column name (1 reply)

LEANDRO AREVALO — Mon, 02 Nov 2020 16:29:26 +0000

Good morning to all

At this moment I have the need to create a trigger, but according to the value returned by the month (coldate) function, it updates a specific column in another table.

I explain:

Table orders
order_id INT auto_increment
client_id INT
order_date DATE
city_id INT

Table summary_month_orders
consecutive INT auto_increment
client_id INT
month1 INT
month2 INT
month3 INT
month4 INT
.....
month12 INT

I need to create a TRIGGER that when inserting a record in the orders summary table adds 1 to the corresponding month field of the month_summary_orders table.
Which means that if I add an order that has the date '2020-10-01', +1 must be added to the month October from the summary_orders_month table.

I try in the following way but so far without success

delimiter //
CREATE TRIGGER add_order AFTER INSERT ON orders
FOR EACH ROW
BEGIN
DECLARE mthdb CHAR(5);
SELECT CONCAT("month",month(NEW.order_date)) INTO mthdb;

UPDATE summary_month_ordersSET mthdb=mthdb +1 WHERE client_id =NEW.client_id

END IF;
END;//
delimiter;

It returns syntax error

Server crashes after trigger execution (4 replies)

Leopold Ramsy — Fri, 23 Oct 2020 15:50:51 +0000

Hi guys,

I have a trigger in MySQL that is causing the MySQL server to restart each time its triggered. This started happening after the version 8.0.22 update a day ago. My trigger is as follows:

CREATE TRIGGER max_client_invoice_before_insert
BEFORE INSERT
ON client_invoices FOR EACH ROW

BEGIN

DECLARE vMax int(11);

SELECT IFNULL(max(client_invoice_id),0) from client_invoices where client_operating_unit_id = NEW.client_operating_unit_id INTO vMax;

SET NEW.client_invoice_id = vMax+1;

END

Is there anything i am missing due to the recent update or any better and efficient ways of achieving the same goal if i can't seem to find the cause of the server crashes?

I have also checked the logs and this is what i find:

06:03:02 UTC - mysqld got signal 11 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x7f08e4921bd0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7f09c8254c70 thread_stack 0x46000
/usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x3d) [0x2194f3d]
/usr/sbin/mysqld(handle_fatal_signal+0x313) [0xff55f3]
/lib64/libpthread.so.0(+0xf630) [0x7f09d5633630]
/usr/sbin/mysqld(Item_splocal::this_item()+0x14) [0x111fce4]
/usr/sbin/mysqld(Item_sp_variable::val_int()+0x13) [0x111fb63]
/usr/sbin/mysqld(Item_func_plus::int_op()+0x1d) [0x11aafdd]
/usr/sbin/mysqld(Item_func_numhybrid::val_int()+0x191) [0x11ad541]
/usr/sbin/mysqld(Item::save_in_field_inner(Field*, bool)+0x125) [0x11259c5]
/usr/sbin/mysqld(Item::save_in_field(Field*, bool)+0x53) [0x113ef03]
/usr/sbin/mysqld(Item_trigger_field::set_value(THD*, sp_rcontext*, Item**)+0x76) [0x113f136]
/usr/sbin/mysqld(sp_instr_set_trigger_field::exec_core(THD*, unsigned int*)+0x90) [0xe38a80]
/usr/sbin/mysqld(sp_lex_instr::reset_lex_and_exec_core(THD*, unsigned int*, bool)+0x60c) [0xe39b1c]
/usr/sbin/mysqld(sp_lex_instr::validate_lex_and_execute_core(THD*, unsigned int*, bool)+0x9a) [0xe3a55a]
/usr/sbin/mysqld(sp_head::execute(THD*, bool)+0x5d3) [0xe311c3]
/usr/sbin/mysqld(sp_head::execute_trigger(THD*, MYSQL_LEX_CSTRING const&, MYSQL_LEX_CSTRING const&, GRANT_INFO*)+0x29d) [0xe31acd]
/usr/sbin/mysqld(Trigger::execute(THD*)+0x10c) [0xfc150c]
/usr/sbin/mysqld(Trigger_chain::execute_triggers(THD*)+0x18) [0xfc28b8]
/usr/sbin/mysqld(Table_trigger_dispatcher::process_triggers(THD*, enum_trigger_event_type, enum_trigger_action_time_type, bool)+0x46) [0xfbc4a6]
/usr/sbin/mysqld(fill_record_n_invoke_before_triggers(THD*, COPY_INFO*, mem_root_deque const&, mem_root_deque const&, TABLE*, enum_trigger_event_type, int, bool, bool*)+0x3f9) [0xe45ac9]
/usr/sbin/mysqld(Sql_cmd_insert_values::execute_inner(THD*)+0x454) [0x1352464]
/usr/sbin/mysqld(Sql_cmd_dml::execute(THD*)+0x525) [0xf15695]
/usr/sbin/mysqld(mysql_execute_command(THD*, bool)+0x9f0) [0xeb98d0]
/usr/sbin/mysqld(Prepared_statement::execute(String*, bool)+0x8f0) [0xee8160]
/usr/sbin/mysqld(Prepared_statement::execute_loop(String*, bool)+0x117) [0xeec5f7]
/usr/sbin/mysqld(mysqld_stmt_execute(THD*, Prepared_statement*, bool, unsigned long, PS_PARAM*)+0x181) [0xeecba1]
/usr/sbin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x1712) [0xebfbf2]
/usr/sbin/mysqld(do_command(THD*)+0x19c) [0xec101c]
/usr/sbin/mysqld() [0xfe69e0]
/usr/sbin/mysqld() [0x272fc3e]
/lib64/libpthread.so.0(+0x7ea5) [0x7f09d562bea5]
/lib64/libc.so.6(clone+0x6d) [0x7f09d3a0e8dd]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f08e5343368): insert into `client_invoices` (`customer_id`, `invoice_date`, `sub_total`, `vat`, `total`, `client_operating_unit_id`, `client_invoice_id`, `invoiced`, `paid`, `created_by`, `updated_by`, `updated_at`, `created_at`) values (459, '2020-10-18 08:03:01', '24202.53', '0', '24202.53', 1, 0, 0, 0, 47, 47, '2020-10-20 08:03:02', '2020-10-20 08:03:02')
Connection ID (thread ID): 743
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
2020-10-20T06:03:04.667817Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2020-10-20T06:03:04.668382Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.22) starting as process 59229
2020-10-20T06:03:04.685040Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-10-20T06:03:07.357601Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-10-20T06:03:08.537376Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2020-10-20T06:03:08.656148Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-10-20T06:03:08.656724Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2020-10-20T06:03:08.733111Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.22' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL.

How I can make the trigger calculates the sum of a column for specific condition (1 reply)

Robin vanpersie — Sun, 04 Oct 2020 17:19:16 +0000

I have table employees as following

CREATE TABLE `employees`
(
  `Employee_Id` Int NOT NULL AUTO_INCREMENT,
  `Employeer_Name` Char(150) NOT NULL,
  `Job` Varchar(100),
  `Salary` Decimal(10,2),
  PRIMARY KEY (`Employee_Id`)
) ENGINE = InnoDB
 AUTO_INCREMENT = 1
 ROW_FORMAT = Compact
;

During a month the employee can borrow some money from his salary many times,
it's required to record that amount of money borrowed , the remaining money from his monthly salary each time.

So I created table Salary_Transaction to record the transactions of borrowing and receiving money

CREATE TABLE `Salary_Transaction`
( `Salary_Transaction_Id` Int NOT NULL AUTO_INCREMENT,
  `money_amount` Decimal(10,2) NOT NULL,
  `Employee_Id` Int,
  PRIMARY KEY (`Salary_Transaction_Id`) );

And I created a table called debt_alerts to display the remaining money from the salary

CREATE TABLE `debt_alerts`
(
  `Debt_alert_id` Int NOT NULL AUTO_INCREMENT,
  `Salary_Transaction_Id` Int,
  `Remaining_money` Decimal(10,2),
  `Employee_Id` Int,
  PRIMARY KEY (`Debt_alert_id`)
) ENGINE = InnoDB
 AUTO_INCREMENT = 5
 ROW_FORMAT = Compact
;

--These are my indexes relationships

CREATE INDEX `IX_Relationship23` USING BTREE ON `debt_alerts` (`Salary_Transaction_Id`)
;

CREATE INDEX `IX_Relationship33` ON `debt_alerts` (`Employee_Id`)
;

CREATE INDEX `IX_Relationship11` USING BTREE ON `salary_transaction` (`Employee_Id`)
;
ALTER TABLE `salary_transaction` ADD CONSTRAINT `Relationship11` FOREIGN KEY (`Employee_Id`) REFERENCES `employees` (`Employee_Id`) ON DELETE CASCADE ON UPDATE CASCADE
;


ALTER TABLE `debt_alerts` ADD CONSTRAINT `Relationship23` FOREIGN KEY (`Salary_Transaction_Id`) REFERENCES `salary_transaction` (`Salary_Transaction_Id`) ON DELETE CASCADE ON UPDATE CASCADE
;

ALTER TABLE `debt_alerts` ADD CONSTRAINT `Relationship18` FOREIGN KEY (`Employee_Id`) REFERENCES `employees` (`Employee_Id`) ON DELETE CASCADE ON UPDATE CASCADE
;

For accomplishment of my task I created this trigger

DELIMITER $$
CREATE trigger Remaining_money_calc
AFTER INSERT ON Salary_Transaction 
FOR EACH ROW 
BEGIN
INSERT INTO debt_alerts(Remaining_money,Employee_Id,Salary_Transaction_Id) 
SELECT y.y-x.x,NEW.Employee_Id ,NEW.Salary_Transaction_Id
FROM ( SELECT SUM(money_amount) x
       FROM Salary_Transaction 
       WHERE Salary_Transaction_Id =  Salary_Transaction_Id ) x
JOIN ( SELECT Employees.Salary y 
       FROM Employees
       WHERE NEW.Employee_Id=Employees.Employee_Id 
       LIMIT 1 ) y;
END
$$ DELIMITER ;

The problem this trigger calculate the sum of all remaining_money in the debt_alerts table, I want it to do that for specific employee only.

Issues with converting update command to trigger (13 replies)

Shawn Logan — Wed, 15 Jul 2020 17:02:28 +0000

Current command:
[sensor_logs]> UPDATE telemetry_log JOIN id_name_update ON telemetry_log.device_id = id_name_update.device_id SET telemetry_log.device_desc = id_name_update.device_desc WHERE telemetry_log.device_desc IS NULL;
(this works)

I would like to create a trigger to do this automatically.
I have tried 100's of possible triggers configurations, all of them ending with a error either 1064 sql syntax or 1442 same table.

table 1 id_name_update
[sensor_logs]> show columns in id_name_update;

+-------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| device_id | char(50) | NO | PRI | NULL | |
| device_desc | char(50) | YES | | NULL | |
| hum_cal | int(11) | NO | | 0 | |
| temp_cal | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+-------+

[sensor_logs]> select * from id_name_update;

+-----------+------------------+---------+----------+
| device_id | device_desc | hum_cal | temp_cal |
+-----------+------------------+---------+----------+
| 11 | PaaRoomByFreezer | 0 | 0 |
| 13 | Compressor | 0 | 0 |
| 15 | PaaLab | 0 | 0 |
| 16 | PottingArea | 0 | 0 |
| 30 | BondFront | 0 | 0 |
| 60 | RightFreezerAir | 0 | 0 |
| 62 | LeftFreezerAir | 0 | 0 |
| 71 | PaaNewRoom | 0 | 0 |
| 72 | BondBack | 0 | 0 |
| 73 | PrimerBooth | 0 | 0 |
+-----------+------------------+---------+----------+

Table 2
[sensor_logs]> show columns in telemetry_log;
+-----------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+----------+------+-----+---------+----------------+
| rec_id | int(11) | NO | PRI | NULL | auto_increment |
| device_id | char(50) | YES | | NULL | |
| type | int(11) | YES | | NULL | |
| value | char(50) | YES | | NULL | |
| date | datetime | YES | MUL | NULL | |
| unit_of_measure | char(1) | YES | | NULL | |
| device_desc | char(50) | YES | | NULL | |
+-----------------+----------+------+-----+---------+----------------+

[sensor_logs]> SELECT * FROM telemetry_log ORDER BY date DESC Limit 10;
+--------+-----------+------+-------+---------------------+-----------------+------------------+
| rec_id | device_id | type | value | date | unit_of_measure | device_desc |
+--------+-----------+------+-------+---------------------+-----------------+------------------+
| 38548 | 72 | 3 | 70.2 | 2020-07-01 10:00:11 | F | NULL |
| 38547 | 72 | 5 | 52.77 | 2020-07-01 10:00:11 | % | NULL |
| 38546 | 72 | 5 | 52.77 | 2020-07-01 09:59:45 | % | BondBack |
| 38545 | 13 | 6 | 3.00 | 2020-07-01 09:58:52 | V | Compressor |
| 38544 | 13 | 3 | 88.36 | 2020-07-01 09:58:52 | F | Compressor |
| 38543 | 11 | 3 | 71.02 | 2020-07-01 09:57:41 | F | PaaRoomByFreezer |
| 38542 | 11 | 5 | 58.63 | 2020-07-01 09:57:41 | % | PaaRoomByFreezer |
| 38541 | 30 | 3 | 70.54 | 2020-07-01 09:57:41 | F | BondFront |
| 38540 | 30 | 5 | 52.50 | 2020-07-01 09:57:41 | % | BondFront |
| 38539 | 71 | 3 | 72.5 | 2020-07-01 09:57:41 | F | PaaNewRoom |
+--------+-----------+------+-------+---------------------+-----------------+------------------+

INSERT INTO on AFTER UPDATE trigger (6 replies)

JK Heydt — Fri, 26 Jun 2020 20:54:03 +0000

I need to append a record into a row_change table for each AFTER UPDATE event trigger. I get an error on the last END statement. Why?

CREATE DEFINER = CURRENT_USER TRIGGER `hto_demo`.`field_AFTER_UPDATE` AFTER UPDATE ON `field` FOR EACH ROW
BEGIN
INSERT INTO row_change
(RowID, TableCd, FieldID, ChangeDateTime, RecordCreatedBit , DataBefore,DataAfter,UserID)
select
new.RowID
,'FLD'
,'110'
,Getdate()
,0
,old.FieldID
,new.FieldID
,system_user()
from field
END

Thank you

mySQL trigger on record that creates a new table (5 replies)

Mike F — Fri, 12 Jun 2020 20:49:46 +0000

It's been quite a few years since I have done DB work. MySql backend (thru workbench) and I am using MS Access as a frontend.

I have a base table that I am multilinking (basically a name column, and # of months column). Through a form, the table is updated (a record added) and I want that new record to trigger an event that creates a table and that table is named based on what the user inputted in the form/record, and create columns for the months - just named month1, month2, etc.

Any thoughts on how to do this? Any thoughts on a better way to do this? lol

I am also looking for recommended reads/videos to get me back up to speed.
Thanks!

MySQL triggers (1 reply)

Jim Sawyer — Mon, 08 Jun 2020 21:27:28 +0000

Can someone please give me an example showing how and where to put a trigger into a MySQL 8 database? All the tutorials seem to forget that we duma**s don't know where the description goes. Just one complete example, please?

Thanks,
Jim Sawyer

Trigger to limit number of rows in another table (5 replies)

Davor Geci — Tue, 26 May 2020 19:10:29 +0000

Can you help me with 2 triggers on 2 tables?
I have Table1 that is having fields:
ID
MaxNumberOfRowsTable2
UserID

Table2 with fields:
ID
Name
UserID

I need to create a trigger on Table1 that will fire every time I change a field MaxNumberOfRowsTable2 and delete all extra rows in Table2 ordered by ID.

For example if Table2 is having 100 rows and we change the MaxNumberOfRowsTable2 field in Table1 to value 15, the trigger should delete all the rows in Table2 and only leave the 15 oldest records.
Can anyone help me with this trigger?

Thanks,
Davor

Need help creating mysql trigger (10 replies)

Larry Hale — Sat, 09 May 2020 16:20:13 +0000

I'm new to using triggers and really not sure how to proceed. I have an access table that a row is inserted whenever someone opens the website or downloads a file with the following structure:

`ID` int(5) NOT NULL autoincrement,
`LOG_TIME` datetime NOT NULL DEFAULT current_timestamp(),
`IP_ADDRESS` int(64) unsigned COLLATE utf8_general_mysql500_ci NOT NULL,
`FILENAME` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL,
`country` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL,
`area` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL,
`city` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci;

When the site is accessed, a row is inserted with only the first 3 fields are filled. When a file is downloaded another row is inserted with the first 4 fields filled. I want to create a trigger such that when a download row is inserted, the IP_ADDRESS is compared to another table to update the country, area, and city fields. I can currently do that for the whole table in mysql monitor using the following code:

Update access t2, ip_lookup t1 set t2.country = t1.country, t2.area = t1.area, t2.city = t1.city
WHERE ((t2.IP_ADDRESS) BETWEEN (t1.start_ip) AND (t1.end_ip)) AND t2.FILENAME is not null and t2.country is null;

How would I write an "after insert" trigger to update the last 3 fields based on the ip of the row that was inserted because of a download?