How to make a "Generic Trigger" in SQL Server?

Question

Hello I have in my database in all tables the following fields

Datetime Criadoem | Datetime Modificadoem

I wonder if you have a way to make a generic Rigger to control these fields, because when insert, the CriadoEm and ModificadoEm will receive the msm value NOW()...

And at the time of update in the tables, only the ModificadoEm will receive NOW()...

It would be a generic Rigger for the after inser and after update tables

It is possible?

Answer 1

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'

Answer 2

Not a solution for Rigger’s update, but maybe good for Criadoem:

(can vary a little from SQL dialect to another, but almost all allow things like)

When creating the table use:

CREATE TABLE T (
   ..definicao dos campos..
   CriadoEm datetime DEFAULT CURRENT_TIMESTAMP
   ModificadoEm datetime DEFAULT CURRENT_TIMESTAMP

Thus, for every new record, the Create and Modify field will get the creation date of the record if you do not enter any value.

As for future updates, there are still two possibilities:

• Even use Trigger

• Or, if your DB allows it, insert null into the modified fieldOn, (and set it to NOT NULL) so that it takes the default value (just testing on your specific DB to see if it works). In Mysql, for example, you should use the type TIMESTAMP for this to happen.

Solution specific to Mysql to keep Modifidoem updated without Trigger:

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 things like.

Answer 3

Unfortunately we can not create a Trigger generic for multiple tables.

However, as the triggers will have the same purpose in all of them, you can generate them automatically, as pointed out by Nulluserexception.

To facilitate your work of creating a Trigger each time it would be interesting, for example, to generate them through a loop that traverses all the tables of your bank:

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

Therefore, by creating the triggers, just replace part of their names and the table they refer to with the variable @TABLE_NAME.

Answer 4

You can create the following Rigger.

CREATE TRIGGER duplica BEFORE INSERT ON contatos
FOR EACH ROW
SET NEW.telefone2 = NEW.telefone;