Trigger SQL Server

Question

I have this Trigger in Postgresql and I’m having a hard time turning it into SQL Server.

-- Trigger --
CREATE TRIGGER livro_original
  BEFORE INSERT
  ON tabela
  FOR EACH ROW
  EXECUTE PROCEDURE insere_valor_original();

CREATE OR REPLACE FUNCTION insere_valor_original()
  RETURNS trigger AS
$BODY$
BEGIN
    IF (new.es_original <= 0 OR new.es_original is null) THEN
        new.es_original = new.id_codigo;
    END IF;
RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

I’m trying this way:

CREATE TRIGGER TRG_EXEMPLO ON tabela FOR INSERT AS 
BEGIN 
  update inserted 
  set INSERTED.es_ORIGINAL = INSERTED.ID_CODIGO 
  where INSERTED.ID_CODIGO = INSERTED.ID_CODIGO 
END 

But I get this mistake:

INSERTED and DELETED logical tables cannot be updated

Answer 1

The error is that you are trying to update the tables sql-server use to control actions in bank, which is not allowed; these tables should be used for consultation only.

To understand the logic:

• If the Trigger for of INSERT, to INSERTED will have records and the DELETED will be empty
• If the Trigger for of DELETE, to INSERTED will be empty and the DELETED will have records
• If the Trigger for of UPDATE, to INSERTED and the DELETED will have records

Understood this, you must use these tables to understand what will be updated on your table (tabela):

CREATE TRIGGER TRG_EXEMPLO ON tabela FOR INSERT AS 
BEGIN 
  UPDATE tabela
  SET tabela.es_ORIGINAL = INSERTED.ID_CODIGO 
  WHERE tabela.ID_CODIGO = INSERTED.ID_CODIGO 
END 

---

However, if I may, I suggest that the "update" is done in the very action of inserting. By the question, you will give a update on the table tabela after a insert on the table tabela, which apparently doesn’t make sense...