SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


trigger


trigger

Author
Message
matt.newman
matt.newman
SSC Eights!
SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)

Group: General Forum Members
Points: 816 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.
river1
river1
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4296 Visits: 1366
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?
river1
river1
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4296 Visits: 1366
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'
Sean Lange
Sean Lange
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60713 Visits: 17954
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 Modens 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)
matt.newman
matt.newman
SSC Eights!
SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)

Group: General Forum Members
Points: 816 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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60713 Visits: 17954
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 Modens 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)
Sean Lange
Sean Lange
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60713 Visits: 17954
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 Modens 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)
Sean Lange
Sean Lange
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60713 Visits: 17954
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 Modens 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)
river1
river1
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4296 Visits: 1366
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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60713 Visits: 17954
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 Modens 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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search