I am making an events system. Let's say an event is "Go dancing" September 12th 2022.

When an event is added to the database we make a unix timestamp on one of the rows. We use default value unix_timestamp() to do this

This timestamp say 654213987 is used as part of the url so people can sign up to event 654213987. People can sign up here for example. http://myevents/signup/654213987

The event organiser writes a description of the event on September 12 2022.

Next year the event organiser wants to do the same event but does not want to rewrite the descriptions. Just duplicate or clone the original without deleting the original.

This would be easy to do programmatically with PHP but I am using XCRUD which I can't modify so my only option is to use triggers or some hard wired part of MYSQL.

When XCRUD makes the duplicate it uses a normal INSERT with a copy of the original minus the primary.

If I make the column UNIQUE it does not allow the clone. If not it makes a duplicate of the timestamp.

Is it possible to make a trigger (or some other mechanism) which would recognise that there is a duplicate and replace the duplicate with another fresh timestamp?

I have seen on stackoverflow that it is possible to add timestamps with triggers but I just can't figure out how to do this to avoid duplicates.

CodePudding user response：

A sample.

CREATE TABLE test (id INT, ts DATE);

CREATE TRIGGER tr_bi_test_add_1_week
BEFORE INSERT ON test
FOR EACH ROW
BEGIN
    WHILE EXISTS ( SELECT NULL
                   FROM test
                   WHERE ts = NEW.ts ) DO
        SET NEW.ts = NEW.ts   INTERVAL 1 WEEK;
    END WHILE;
END

INSERT INTO test VALUES (1, '2022-01-01'); 
-- inserted as-is
SELECT * FROM test;

INSERT INTO test VALUES (2, '2022-01-02'); 
-- inserted as-is
INSERT INTO test VALUES (3, '2022-01-15'); 
-- inserted as-is
SELECT * FROM test;

INSERT INTO test VALUES (4, '2022-01-01'); 
-- 2022-01-01 is present => 2022-01-08
SELECT * FROM test;

INSERT INTO test VALUES (5, '2022-01-01'); 
-- 2022-01-01, -08, -15 is present => 2022-01-22
SELECT * FROM test;

fiddle