Sqlserver Trigger after update

Asked

Viewed 489 times

3

Let’s imagine the following scenario:

CREATE TABLE invalido (
   ds_valor character varying(255)
)

CREATE TABLE email (
    email character varying(255)
)

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

Same situation as: Postgresql Trigger apos update but with Sqlserver

sql-server trigger
  • 2

    David, can you add the question what you tried so far?

2 answers

7


Basically, this is it

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

  • Looped :(

  • Now, is the structure exactly that of the question? I replicated the structure here and it works normal.

  • I’ll see if there’s any other Rigger that might have bugged...

  • Update Email Set email = 'invalid' From Inserted; this doesn’t need Where? I put Where and it worked

1

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

You can create a trigger with inner join on the table invalido, ensuring that only invalid records will be 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

Observing: If there is a record in the table invalido with the value email invalido to trigger will go into looping infinite.

Browser other questions tagged sql-server trigger

You are not signed in. Login or sign up in order to post.