Use triggers to generate Unix timestamps on insert while allowing cloning of existing records and avoiding duplication

Question

I'm making an event system. Let's say there is an event called "Go Dance" on September 12, 2022.

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

This timestamp (e.g. 654213987) is used as part of the URL so that people can register for event 654213987. For example, people can register here. http://myevents/signup/654213987

The event organizer wrote the event description on September 12, 2022.

Event organizers want to host the same event next year but don't want to rewrite the description. Just copy or clone the original file without deleting it.

This is easily done programmatically in PHP, but I'm using XCRUD and I can't modify it, so my only options are to use triggers or some hardwired part of MYSQL.

When XCRUD makes a copy, it uses a normal INSERT containing the original copy minus the master copy.

If I make the column unique, it does not allow cloning. If not, it copies the timestamp.

Is it possible to create a trigger (or other mechanism) to recognize that a duplicate exists and replace the duplicate with another new timestamp?

I saw on stackoverflow that you can add timestamps using triggers, but I just don't know how to do it to avoid duplication.

Answer 1

Example.

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;

id	ts
1	2022-01-01