Hello, I have in my database in all tables the following fields
DateTime CreatedEm | Modified DateTime
I wanted to know if there is a way to do a generic trigger to control these fields, since
when you enter, CriadoEm and ModificadoEm will receive the msm value NOW () ...
And at the time of the update in the tables, only ModificadoEm will receive NOW () ...
It would be a generic trigger for the after insert and after update tables
Is it possible?
It is not a solution for the update trigger, but maybe good for the CreatedEm:
(may vary slightly from SQL dialect to another, but almost all allow such thing)
When creating the table use:
CREATE TABLE T (
..definicao dos campos..
CriadoEm datetime DEFAULT CURRENT_TIMESTAMP
ModificadoEm datetime DEFAULT CURRENT_TIMESTAMP
So, for every new record, the CreatedEm and ModifiedEm field will get the record creation date if you do not enter any value.
As for future updates, there are still two possibilities:
Use same trigger
Or, if your DB allows, insert null into the modifiedEm field (and set it to NOT NULL) so that it takes the default value (only testing on your specific DB to see if it works) . In MySQL, for example, you should use type TIMESTAMP to make this happen.
CREATE TABLE t1 (
..definição dos outros campos..
ModificadoEm TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
It remains to see if the SQL you are using has something like that.
The following script creates triggers for all tables in a database:
exec sp_MSForEachTable 'CREATE TRIGGER [?_Update] ON ? FOR UPDATE AS
BEGIN SET NOCOUNT ON update ? set ModificadoEm = GETDATE()
from ? updatedTable inner join inserted i
on i.$identity = updatedTable.$identity
END'
Unfortunately, you can not create a generic trigger for multiple tables.
However, since triggers will have the same purpose in all of them, you can generate them automatically, as pointed out by NullUserException.
To make it easier to create one trigger at a time, it would be interesting, for example, to generate them through a loop that ran through all the tables in your database:
declare @TABLE_NAME sysname
select @TABLE_NAME = min(TABLE_NAME) from seuBanco.information_schema.tables
while (@TABLE_NAME is not null)
begin
/*Criar trigger after insert*/
/*Criar trigger after update*/
select @TABLE_NAME = min(TABLE_NAME) from seuBanco.information_schema.tables
where TABLE_NAME > @TABLE_NAME
end
So when creating triggers , it would be enough to replace part of their names and the table they refer to with the variable @TABLE_NAME .
You can create the following trigger.
CREATE TRIGGER duplica BEFORE INSERT ON contatos
FOR EACH ROW
SET NEW.telefone2 = NEW.telefone;