CODEWITHSUNDEEP
mysqltriggers
stackattack
stackattack

Reputation: 354

AFTER INSERT trigger on same table

I have a table with 4 columns:

ID, start_time, time, end_time

Now if I want to insert rows into my table then end_time should be filled automatically. So I only have to supply two values (ID is inserted automatically): start_time and time.

To do that I use DATE_ADD function.

date_add(start_time, interval time minute)

I want to use AFTER INSERT trigger to do that, but I don't know how to create that trigger.

Thanks in advance!

EDIT: DATE_ADD

Upvotes: 0

Views: 1340

Answers (2)

Oldskool
Oldskool

Reputation: 34837

If you use the proper datatypes (DATETIME / TIME), you can do the DATE_ADD in the query directly as well. For example, if you'd want to set the end_time to be start_time + time, you could do this in your insert statment:

INSERT INTO `table` (`start_time`, `time`, `end_time`)
VALUES (
    '2015-04-22 13:37:00',
    '02:30:00',
    DATE_ADD(`start_time`, INTERVAL `time` HOUR_SECOND)
);

That would set end_time to 2015-04-22 16:07:00 instantly.

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

You can not update the same table where the trigger is being executed. So you need to use before insert

delimiter //
create trigger table_before_insert before insert on table_name
for each row
begin
 set new.end_time = date_add(/*your increment logic here */);
end;//

delimiter;

you can access all the values using new and use them inside the date_add function as needed.

Upvotes: 2

Related Questions