In PostgreSQL we can have triggers like this:
CREATE TRIGGER tr_info_changed
BEFORE INSERT OR UPDATE
ON t_info
FOR EACH ROW
EXECUTE PROCEDURE tf_table_changed();
With tf_table_changed() beeing a Trigger Function like this:
CREATE OR REPLACE FUNCTION tf_table_changed()
RETURNS trigger AS
$BODY$
BEGIN
IF (TG_OP = 'INSERT') THEN
-- Keep track of record insertion.
NEW.created = current_timestamp;
NEW.changed = NEW.created; -- Times of change and insertion are identical.
RETURN NEW;
END IF;
IF (TG_OP = 'UPDATE') THEN
-- Keep track of changes.
NEW.created = OLD.created; -- EF would overwrite value.
NEW.changed = current_timestamp;
RETURN NEW;
END IF;
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
In Transact-SQL I have this so far:
CREATE TRIGGER [dbo].[Trigger_Setting_Created]
ON [dbo].[T_Setting] AFTER INSERT
AS
UPDATE s SET Created = GETDATE(), Changed = GETDATE()
FROM T_Setting AS s
INNER JOIN inserted AS i
ON s.SessionToken = i.SessionToken
GO
I ommit the second trigger for the update case.
As we can see when we want to keep all Created/Updated up to date in each and every of our tables it is getting very cumbersome in Transact-SQL with lots of copy&paste and modifying each trigger hopefully not forgetting any table-specific changes whereas in PostgreSQL things are DRY and simple.
Question: Is there an equivalent in Transact-SQL with respect for DRYness?
