使用触发器在多个表中存档已删除的记录

I am using MySQL 5.0.95. I have a web form where customers can create a guest list for their event, the guest list will be approved by the office manager, and from that the front desk gets the day's approved visitors.

我使用的是MySQL 5.0.95。我有一个网络表单,客户可以为他们的活动创建一个客人名单,客人名单将由办公室经理批准,并由此前台获得当天批准的访客。

I have boiled the problem down to save space. I have two tables, _events and _guests. I also have two matching archive tables.

我把问题煮沸以节省空间。我有两个表,_events和_guests。我还有两个匹配的归档表。

CREATE TABLE IF NOT EXISTS visitor._events (
eventId int( 11 ) NOT NULL AUTO_INCREMENT,
eventName varchar( 200 ) NOT NULL,
PRIMARY KEY ( eventId ) 
);

CREATE TABLE IF NOT EXISTS  visitor._archived_events (
eventId int( 11 ) NOT NULL,
eventName varchar( 200 ) NOT NULL,
PRIMARY KEY ( eventId ) 
);

CREATE TABLE IF NOT EXISTS  visitor._guests (
guestId int( 11 ) NOT NULL AUTO_INCREMENT,
eventId int( 11 ) NOT NULL,
guestName varchar( 200 ) NOT NULL,
PRIMARY KEY ( guestId ) 
);

CREATE TABLE IF NOT EXISTS  visitor._archived_guests (
guestId int( 11 ) NOT NULL,
eventId int( 11 ) NOT NULL,
guestName varchar( 200 ) NOT NULL,
PRIMARY KEY ( guestId ) 
);

I want the office manager to be able to delete events (and their guest records) from the database once the event is over, but I'd like those events to be archived for the year for records management.

我希望办公室经理能够在事件结束后从数据库中删除事件(及其客人记录),但我希望这些事件在一年中存档以进行记录管理。

I've created a TRIGGER that will copy any record DELETED from the _guests table into the _archive_guests table.

我创建了一个TRIGGER,它将把_guests表中的任何记录DELETED复制到_archive_guests表中。

DELIMITER $$

DROP TRIGGER IF EXISTS TR_A_DEL_guests $$

CREATE TRIGGER TR_A_DEL_guests DELETE ON _guests FOR EACH ROW BEGIN

 INSERT IGNORE INTO _archived_guests (
   guestId,
   guestName
 ) VALUES (
   OLD.guestId,
   guestName
 );

END $$

DELIMITER ;

I also have a TRIGGER that will DELETE all guest records for the specific eventID from the _guests table and then copy any DELETED record from the _events table into the _archived_events table.

我还有一个TRIGGER,它将从_guests表中删除特定eventID的所有客户记录,然后将_events表中的任何DELETED记录复制到_archived_events表中。

DELIMITER $$

DROP TRIGGER IF EXISTS TR_A_DEL_events $$

CREATE TRIGGER TR_A_DEL_events AFTER DELETE ON _events FOR EACH ROW BEGIN

 DELETE FROM _guests WHERE eventId = OLD.eventId;

 INSERT IGNORE INTO _archived_events (
   eventId,
   eventName
 ) VALUES (
   OLD.idEventId,
   OLD.eventName
 );

END $$

DELIMITER ;

The upshot is that when a record is deleted from _events, it is moved to _archived_events and all the _guests records for that event are copied to the _archived_guests table.

结果是,当从_events中删除记录时,会将其移至_archived_events,并将该事件的所有_guests记录复制到_archived_guests表。

The problem I have is that now, when the customer is editing their guest list, if they delete a guest record, it is archived. They are able to edit/delete guests but not delete the event altogether.

我遇到的问题是,现在,当客户正在编辑其访客列表时,如果他们删除访客记录,则会将其存档。他们可以编辑/删除访客,但不能完全删除该事件。

My question: Is there any way to use only one TRIGGER on deleted events that will first copy each of the appropriate _guest records (eventId = OLD.eventId) to _archived_guests and then copy the _event record to _archived_events?

我的问题:有没有办法在删除的事件上只使用一个TRIGGER,它会首先将每个适当的_guest记录(eventId = OLD.eventId)复制到_archived_guests,然后将_event记录复制到_archived_events?

1 个解决方案

#1

If I understand you correctly try

如果我理解你正确的尝试

DELIMITER $$

DROP TRIGGER IF EXISTS TR_A_DEL_events_guests $$
CREATE TRIGGER TR_A_DEL_events_guests 
BEFORE DELETE ON _events 
FOR EACH ROW 
BEGIN
    -- Archive guests
    INSERT IGNORE INTO _archived_guests (guestId, eventId, guestName)
    SELECT guestId, eventId, guestName
      FROM _guests
     WHERE eventId = OLD.eventId;
    -- Delete guests 
    DELETE 
      FROM _guests 
     WHERE eventId = OLD.eventId;
    -- Archive event before deleting
    INSERT IGNORE INTO _archived_events (eventId, eventName)
    VALUES (OLD.eventId, OLD.eventName);
END $$

DELIMITER ;

Here is SQLFiddle demo

这是SQLFiddle演示

#1