How to refer to "New", "Old" row for Triggers in SQL server?

Question

SO I'm new to SQL server from SQLite and I am used to using the New|Old keywords. I have seen that some people use the inserted value refer to a newly created row, but this would only apply on an insert and not an update. Howe can I get something like the New I use in this query?

create trigger ports_country_id_in_check
on [SISTEMA].Puerto
after insert, update
AS
BEGIN
  update [SISTEMA].Puerto
  set country_id = (select secuencia from [SISTEMA].Pais where codigo = New.pais_asociado)
  where [SISTEMA].Puerto.secuencia = New.secuencia
end

`INSERTED` is the new row on `INSERT/UPDATE`. `DELETED` is the deleted row on `DELETE` and the updated row on `UPDATE` (i.e. the old values before the row was updated) — Charleh, Mar 05 '13 at 16:13
Have you reviewed the [documentation](http://msdn.microsoft.com/en-us/library/ms191300.aspx) explaining the `inserted` (and `deleted`) table? — Pondlife, Mar 05 '13 at 16:16

score 3 · Accepted Answer · answered Mar 05 '13 at 16:14

3

Inserted also will also apply to update. One updated row will be seen as a deleted and an inserted row. So you can check both what was and what it is now.

answered Mar 05 '13 at 16:14

Dumitrescu Bogdan

7,127
2
23
31

score 0 · Answer 2 · edited Dec 10 '13 at 03:07

create trigger ports_country_id_in_check
on [SISTEMA].Puerto
after insert, update
AS
BEGIN
  Declare @pais_asociado, @secuencia int

  select @pais_asociado = Puerto.pais_asociado, @secuencia = Puerto.secuencia
  from Puerto join inserted
  where Puerto.secuencia = inserted.secuencia

  update [SISTEMA].Puerto
  set country_id = (select secuencia from [SISTEMA].Pais where codigo = @pais_asociado)
  where [SISTEMA].Puerto.secuencia = @secuencia
end

How to refer to "New", "Old" row for Triggers in SQL server?

2 Answers2

Linked