Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

trigger Expand / Collapse
Author
Message
Posted Tuesday, October 30, 2012 8:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 1:58 PM
Points: 178, Visits: 473
update c
set c.nif = (needs reference or is ambiguous).nif
from contribuintes c
join...etc...


Either the above with reference, or just take out the c and preface with full name. Sorry for little detail, just getting up and running this morning.
Post #1378814
Posted Tuesday, October 30, 2012 8:50 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 4:09 AM
Points: 761, Visits: 1,083
The code:

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


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?
Post #1378818
Posted Tuesday, October 30, 2012 8:52 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 4:09 AM
Points: 761, Visits: 1,083
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'
Post #1378821
Posted Tuesday, October 30, 2012 8:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:05 PM
Points: 13,320, Visits: 12,804
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'


Think you need to set this to d.nif and not c.nif.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1378825
Posted Tuesday, October 30, 2012 8:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 1:58 PM
Points: 178, Visits: 473
Sorry, trying to do a few things but overall

declare @insertednif and @deletednif

select via select

if insertednif <> deletednif
begin
rollback
return
end
Post #1378826
Posted Tuesday, October 30, 2012 8:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:05 PM
Points: 13,320, Visits: 12,804
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


no no no. You do not want to use variables like this in a trigger. That is a sign of only handling single row updates. You need to deal with the whole set.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1378828
Posted Tuesday, October 30, 2012 8:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:05 PM
Points: 13,320, Visits: 12,804
river1 (10/30/2012)
The code:

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


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?


You need to look at inserted to know if the new value of numeroposto = 'Central'. It actually doesn't matter if the new value for nif is the same or not, you want the old value either way. Keep in mind I can't actually test anything here, I am coding 100% blind because we don't have any ddl.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1378833
Posted Tuesday, October 30, 2012 9:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:05 PM
Points: 13,320, Visits: 12,804
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




_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1378868
Posted Tuesday, November 6, 2012 3:51 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 4:09 AM
Points: 761, Visits: 1,083
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





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.
Post #1381475
Posted Tuesday, November 6, 2012 8:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:05 PM
Points: 13,320, Visits: 12,804
river1 (11/6/2012)
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





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.


Have you tested this code? It does NOT update the entire table. It is using the inserted and deleted tables. Those will contain the row(s) that were updated.

I think you just need to change your join to use nif_antigo??? I don't know what your keys are because you still have not posted any ddl and sample data. I have coded this completely in a vacuum.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1381590
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse