It would be fairly easy but I would actually recommend changing the logic of the command that is inserting/updating the data so that it adds the additional information at this point.
However, should you wish to proceed with a trigger you could do something like this:
create trigger my_trigger
on my_table
after insert, update
as
begin
declare @dt datetime = getdate();
update a
set expire_date = CONVERT(DATE, @dt, 101),
expire_time = CONVERT(TIME, @dt)
from my_table as a
join inserted as b
on a.product_no = b.product_no;
end
go
This uses the INSERTED
table to find out the product_no
of the rows that have been changed/created. You can find out more about triggers at the link below:
http://msdn.microsoft.com/en-us/library/ms189799.aspx
I hope this helps you.
How about expanding this a little and do a proper log table, logging every update, insert or delete. That way you can keep a proper history.
For this, in MySQL, you will need three triggers :
CREATE TABLE EMP ( ID INT,
LASTNAME varchar(100),
firstname varchar(100),
gender varchar(1),
dob date,
marital varchar(1),
ssn varchar(20)
);
CREATE TABLE EMPLOG ( empid INT,
operation varchar(1),
operationdate date,
operated_by varchar(200),
LASTNAME varchar(100),
firstname varchar(100),
gender varchar(1),
dob date,
marital varchar(1),
ssn varchar(20)
);
CREATE TRIGGER emplog_insert AFTER INSERT ON emp
FOR EACH ROW
INSERT INTO emplog VALUES
(NEW.id,
'I',
Now(),
USER(),
NEW.lastname,
NEW.firstname,
NEW.gender,
NEW.dob,
NEW.marital,
NEW.SSN
);
CREATE TRIGGER emplog_update AFTER UPDATE ON emp
FOR EACH ROW
INSERT INTO emplog VALUES
(NEW.id,
'U',
Now(),
USER(),
NEW.lastname,
NEW.firstname,
NEW.gender,
NEW.dob,
NEW.marital,
NEW.SSN
);
CREATE TRIGGER emplog_delete AFTER DELETE ON emp
FOR EACH ROW
INSERT INTO emplog VALUES
(OLD.id,
'D',
Now(),
USER(),
OLD.lastname,
OLD.firstname,
OLD.gender,
OLD.dob,
OLD.marital,
OLD.SSN
);
If you then wish to check out the history of what happened for a specific employee, you simply run this query :
select *
from Emplog
where empid = ?
order by operationdate
Best Answer
Create a task in SQL Server Agent that runs a stored procedure once every minute that updates the affected rows.
For instance, something like this might work: