update
contribuintes
set
nif = c.nif
from
contribuintes c
join
inserted i
on
i.nif_antigo = c.nif_antigo
where
i.numeroposto = 'Central'

river1 (10/30/2012)

---

like this:

update
contribuintes
set
nif = c.nif
from
contribuintes c
join
deleted d
on
d.nif_antigo = c.nif_antigo
where
c.numeroposto = 'Central'

matt.newman (10/30/2012)

---

Sorry, trying to do a few things but overall

declare @insertednif and @deletednif

select via select

if insertednif <> deletednif
begin
rollback
return
end

river1 (10/30/2012)

---

The code:



Has a problem.

I want that If the value from NIF is changed, then it needs to get back to the original value again.

So, why use the inserted instead of the deleted table?

Sean Lange (10/30/2012)

---

I think this should do what you are looking for but it is untested because I don't have ddl to work with.

create trigger TRG_CONTRIBUINTES_VERIFCA_RF_E_NIF on contribuintes
for update
as begin

	update contribuintes
	set nif = d.nif
	from inserted i 
	join deleted d on d.SomeID = i.SomeID
	where i.numeroposto = 'Central' 
	
	update contribuintes
	set cod_rep_fiscal = d.cod_rep_fiscal
	from inserted i 
	join deleted d on d.SomeID = i.SomeID
	where i.cod_rep_fiscal not in (select cod_rep_fiscal from V_RETURN_RF) 
end

river1 (11/6/2012)

---

I think that we have a problem with that code sample....

How will the trigger knows which NIFs should it updates on table contribuintes?

Example:


My table contribuintes have the following columns:


NIF; NIF_ANTIGO (it's the primary key); NAME; ADDRESS; COD_REP_FISCAL,etc...


When I update a NIF, the trigger should only update the NIF column with values from the deleted table to NIFs where nif_antigo is the same as the nif_antigo from deleted table.

Otherwise, When an update happens, all the fields NIF from the table contribuintes will be updated with the NIF from the deleted table.

This is not correct.

Only does NIFs that were updated should get their values back to normal (nifs on the deleted table).

The key to do this is the nif_antigo present on tables deleted and contribuintes.

How can I change the code do accomplish this?

Thank you very much.