SQL Server 2005 triggers

Question

SQL Server 2005 triggers

river1

SSC-Insane

Points: 24315
More actions
October 20, 2010 at 9:27 am

#142245

Hi,
I have a app of witch i do not have the source code...
Some guys update several fields of the database throught this app.
One of this fileds is the "BI". I do not want that people can update this field "BI" through the app in a particular situation.
Particular situation:
This table as several fields , as i told, and when the field "BI" is updated to a new value some times the app updates the field "POSTO" to a value = "CENTRAL".
I think that the app is doing something like:
Update table set bi ='New value given by the user', Posto ='Central', Date =getdate()
So, when the field posto is equals to "Central" then the table should not permite the update. But if the filed POSTO is going to have another value, like 'Others' then the update can be done.
How can i do this using a trigger?
Can sameone help?
Thank you

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

i see two solutions here...raise an error or put the old value for the BI column back, and allow the transaction to go through.

something like this would simply put the BI column's value back only if the Posto column was set to that value.....

CREATE TRIGGER myTrigger

ON SomeTable

FOR UPDATE

AS

BEGIN

--any rows that set Posto ='Central' need to put the original value back into the BI column.

UPDATE SomeTable

SET SomeTable.BI = DELETED.BI

FROM DELETED

WHERE SomeTable.PK = DELETED.PK

AND SomeTable.Posto ='Central'

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

river1 SSC-Insane Points: 24315 More actions · Answer 2

that seems a great solution.

But i will have a issue with performance, at least that is what i think.

Can you correct me please, if i am not correct?

Let me explain my concern:

It the field "BI" is updated through the app, by a user, then the POSTO field would have 'Central' and if i create that trigger on my table, then the user can no longer update through the app, because the triiger is fired and the original values are back again!

THis is very good.

But, serveral times, i update this field by importing data that is in XML files through the same app.

In this case (when i am importing data), when the update of the field BI is done, the field "POSTO" is updated to 'SG'

So, the update will work fine as i want.

But, i thing that because of the trigger that i will create on this table, the performance of my inserts will go down...

Because the trigger, after each insert of values will have to see if it needs to be fired or not, am i correct?

Thank you.