how to getting column or field name

guys...i want to get field name from table with trigger...i mean when
i update field(s).. i get the field name
to insert into table history..so i can make history with field:
update_by, update_date, field_name, Old_value,new_value.

On Tue, 26 Jun 2007 20:50:42 -0700, renyald <ndotuk@gmail.com>
wrote:

>guys...i want to get field name from table with trigger...i mean when
>i update field(s).. i get the field name
>to insert into table history..so i can make history with field:
>update_by, update_date, field_name, Old_value,new_value.

There are better ways to make an audit trail.

Joe Celko has a good chapter on temporal data
in his book "SQL for smarties" [1].

Some time ago I found a bash script from Mike Chirico
which maintains a notes database:
http://souptonuts.sourceforge.net/code/n.html
and changed it to my liking as a
demonstration of temporal data [2].

Editing it for publication here may have introduced some
mistakes, testing is left to you

.... but I'm not sure this may solve your problem.
Your proposal has a potential problem with the data type of the
changed values, usually not every column has the same type.

An alternative is to have the trigger store a copy of the
complete row on every update; optionally set unchanged columns
to NULL using CASE constructs.
In a variable length row, a NULL doesn't take much space.

References and quotes:
[1]
Google for ISDN-13: 978-0-12-369379-2

[2]
-- Syntax is sqlite (close to SQL 92),
-- http://www.sqlite.org/lang.html
-- converting to MySQL is left as
-- an exercise for the reader.
-- define schema
BEGIN TRANSACTION;

DROP TABLE IF EXISTS notes;
CREATE TABLE notes (
-- ROWID, you may need AUTO_INCREMENT here,
-- but a real candidate key will be even better.
nkey INTEGER PRIMARY KEY,
-- category left out in this demo
msg TEXT
);

-- '9999-12-31 23:59:59' means 'end-of-times'
DROP TABLE IF EXISTS audit;
CREATE TABLE audit (
nkey INTEGER,
-- category left out in this demo
msg TEXT,
timeFr DATETEXT,
timeTo DATETEXT DEFAULT '9999-12-31 23:59:59',
PRIMARY KEY (nkey,timeFr)
);

CREATE TRIGGER audit_insert_notes
AFTER INSERT ON notes FOR EACH ROW
BEGIN
INSERT INTO audit (nkey, msg, timeFr)
VALUES (new.nkey, new.msg, CURRENT_TIMESTAMP);
END;

CREATE TRIGGER audit_update_notes
AFTER UPDATE ON notes FOR EACH ROW
BEGIN
UPDATE audit SET timeTo = CURRENT_TIMESTAMP
WHERE nkey == new.nkey
AND timeTo == '9999-12-31 23:59:59';
INSERT INTO audit (nkey, msg, timeFr)
VALUES (new.nkey, new.msg, CURRENT_TIMESTAMP);
END;

CREATE TRIGGER audit_delete_notes
AFTER DELETE ON notes FOR EACH ROW
BEGIN
UPDATE audit SET timeTo = CURRENT_TIMESTAMP
WHERE nkey == old.nkey
AND timeTo == '9999-12-31 23:59:59';
END;

DROP VIEW IF EXISTS sh_audit;
CREATE VIEW sh_audit AS
SELECT *,
strftime('%H:%M:%S',
(strftime('%s',CASE timeTo
WHEN '9999-12-31 23:59:59' THEN CURRENT_TIMESTAMP
ELSE timeTo
END) - strftime('%s',timeFr)),'unixepoch') AS lifespan
FROM audit
ORDER BY nkey,timeFr;

DROP VIEW IF EXISTS sh_status;
CREATE VIEW sh_status AS
SELECT
COUNT(CASE
WHEN timeTo == '9999-12-31 23:59:59' THEN 1
ELSE NULL
END) AS active,
COUNT(CASE
WHEN timeTo < CURRENT_TIMESTAMP THEN 1
ELSE NULL
END) AS deleted
FROM audit
GROUP BY nkey;

COMMIT; -- done definitions

-- test data
INSERT INTO notes (msg) VALUES ('note 1 version 1');
INSERT INTO notes (msg) VALUES ('note 2 version 1');
UPDATE notes SET msg = 'note 1 version 2' WHERE nkey == 1;
UPDATE notes SET msg = 'note 2 version 2' WHERE nkey == 2;
UPDATE notes SET msg = 'note 1 version 3' WHERE nkey == 1;

-- queries
SELECT * FROM sh_audit;
SELECT * FROM sh_status;

SELECT 'notes';
SELECT * FROM notes;

SELECT 'audit';
SELECT * FROM audit;

SELECT 'audit with timediff';
SELECT * FROM sh_audit;

--
( Kees
)
c[_] All wiyht. Rho sritched mg kegtops awound? (#104)