Description:
Suppose I wish to retrofit an audit trail to this table
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
lastname CHAR(32) NOT NULL,
firstname CHAR(32) NOT NULL,
gender CHAR(1),
dob datetime,
marital CHAR(1),
SSN CHAR(9)
);
INSERT INTO emp VALUES
(1, 'Black', 'Mary', 'F', '1972-10-31', 'M', '135792468'),
(2, 'Higgins', 'Henry', 'M', '1955-2-28','W','246813579'),
(3, 'Turunen', 'Raija', 'F', '1949-5-15','M','357902468'),
(4, 'Garner', 'Sam', 'M', '1964-8-15','M','468013579');
using transaction-time-valid logic rather than a (redundant) tracking log. The preferred method is to replace my emp table with a transaction-valid version of it ...
CREATE TABLE emp_ts LIKE emp;
ALTER TABLE emp_ts ADD COLUMN start_ts TIMESTAMP NULL;
ALTER TABLE emp_ts ADD COLUMN end_ts TIMESTAMP NULL;
ALTER TABLE emp_ts ADD COLUMN started_by VARCHAR(77);
ALTER TABLE emp_ts ADD COLUMN ended_by VARCHAR(77);
ALTER TABLE emp_ts MODIFY COLUMN id INT DEFAULT 0;
ALTER TABLE emp_ts DROP PRIMARY KEY;
ALTER TABLE emp_ts ADD PRIMARY KEY (id, end_ts );
INSERT INTO emp_ts
SELECT id,lastname,firstname,gender,dob,marital,ssn,’2008-8-15’,'2037-12-31',USER(),NULL FROM emp;
then drop the emp table altogether, replacing it with a View:
DROP TABLE emp;
CREATE VIEW emp AS
SELECT id,lastname,firstname,gender,dob,marital,ssn
FROM emp_ts
WHERE end_ts = '2037-12-31';
SELECT * FROM emp;
In in trail transaction time logic, an UPDATE resolves to a UPDATE plus an INSERT, and a DELETE resolves to an UPDATE, so to implement this audit trail transparently---ie without having to change the application's DB interface--I need INSTEAD OF Triggers of the sort implemented for many years by both
Oracle and SQL Server.
Alas MySQL does not implement INSTEAD OF Triggers. We are forced to use sprocs instead. Much more awkward!
I suggest that the increasing demand for such audit trail logic makes a strong argument for this feature request.
How to repeat:
As above
