SQLServer Trigger after update

Question

SQLServer Trigger after update

Navigation

#1 by (7 votes)
#2 by (1 votes)

3

Let's imagine the following scenario:

CREATE TABLE invalido (
   ds_valor character varying(255)
)

CREATE TABLE email (
    email character varying(255)
)

I now need a trigger that every time I run a UPDATE or INSERT in the email table it checks if the value is in the invalido table, if it is then it should replace what would be inserted in the email table for invalid email .

Same situation as: PostgreSQL Trigger post update but with SQLServer

sql-server trigger

asked by anonymous 30.11.2016 / 14:23

2 answers

1

The ideal way for the structure to work was that its email table had a single id , so it would be possible to do with a trigger instead of that would do the processing before even insert or update .

You can create a trigger with inner join in the invalido table, ensuring that only invalid records are changed.

CREATE TRIGGER trg_after_iu_email ON email AFTER INSERT, UPDATE
AS
BEGIN
  set nocount on;

  UPDATE ema
     SET ema.email = 'email invalido'
    FROM email ema
         INNER JOIN inserted ins on ins.email = ema.email
         INNER JOIN invalido inv on inv.ds_valor = ins.email;
END;
GO

Note: If there is a record in the invalido table with the value email invalido to trigger it will enter looping infinity.

30.11.2016 / 16:46

Why does the main method receive parameters? [duplicate] Cast of byte type integer

score 7 · Accepted Answer

Basically, this is it here

CREATE TRIGGER [dbo].AfterUpdateOrInsertEmail 
ON [dbo].email

AFTER INSERT, UPDATE
AS 
BEGIN

    IF EXISTS (SELECT * FROM [dbo].invalido inv WHERE inv.ds_valor = (Select Inserted.Email From Inserted))
    BEGIN
        Update Email Set email = 'inválido' From Inserted;
    END
END