December 6, 2005 at 3:04 am
I have several tables that have an ID of 0. These are here to help with referential integrity in a database that had none when we took over. The application we run against this database for some reason updates the 0 record at times. What I want to do is ignore that update. I don't ever want the 0 record updated.
How would I write a trigger that would stop the 0 record from being changed?
December 6, 2005 at 3:18 am
If you are using SQL-Server-2000, then there are these 'Instead Of' triggers. 'Instead Of ' Triggers fire instead of the operation that fires the trigger.
An update opertaion would fire this trigger and you can update the tables only if you write code to update in this trigger. Otherwise there will be no updation.
December 6, 2005 at 3:20 am
Could you give me a sample "Instead of" trigger?
thanks!
December 6, 2005 at 3:54 am
http://www.windowsitpro.com/SQLServer/Article/ArticleID/9734/9734.html
Download the zip in this article.
December 9, 2005 at 9:56 pm
I tried the following trigger:
create TRIGGER trg_u_address ON Address INSTEAD OF UPDATE
AS
-- Make sure that all of the data meets the CHECK constraint.
IF EXISTS(SELECT *
FROM inserted
WHERE id = 0)
begin
update address set description='', customerid = 0, housenumber = '', prefix='', street='', suffix='', apartment='', city='', state='', zip='', zone=0, businessid=0, active=0, contact='' where id = 0
end
what happened is that anytime an address record was updated, the updates were not saved at all. It wasn't just blocking the id = 0 records. What did I do wrong?
Thanks!
December 10, 2005 at 8:04 am
Use an else statement and perform the update again. The reason you need to do this is the INSTEAD OF trigger fires before the data operation in the event that you want to block the operation (which you do). Unless you explicitly tell it to perform the operation in the INSTEAD OF trigger (which you would in the ELSE block), it's going to assume you just want to stop things altogether.
K. Brian Kelley
@kbriankelley
December 10, 2005 at 10:23 am
OK, this is all new to me, so please bear with me. How do I do the update in the ELSE? I looked at the sample and don't see where they do that. Any help would be appreciated.
December 10, 2005 at 11:54 am
Triggers become easy to write once the concept is understood. Please open SQL ServerBooks Online and for the index tab, enter "inserted tables". Here is the first paragraph:
"Two special tables are used in trigger statements: the deleted table and the inserted table. Microsoft® SQL Server™ 2000 automatically creates and manages these tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for trigger actions; however, you cannot alter the data in the tables directly."
With "instead of" triggers, the trigger is responsible for updating the underlying tables. The inserted table will contain what the base table changes would be like if you did not have an "instead of" trigger.
Note that trigger always run even when the number of rows affected is zero. The trigger logic needs to check for this case.
"What I want to do is ignore that update". Do you want to ignore or cause the update to fail ? I would suggest that there is a program bug and the wrong row is being updated and therefore, then action to take is fail the update.
create TRIGGER Address_TUI -- Trigger Update Instead
ON Address INSTEAD OF UPDATE
AS
set nocount on
set xact_abort on
-- When no rows updated, exit
IF 0 = (select count(*) from inserted) return
-- If attempting to update the special row - ABORT and exit.
IF EXISTS(SELECT *
FROM inserted
WHERE id = 0)
begin
ROLLBACK TRANSACTION
RAISERROR ('Application attempting to update CustomerId of zero', 16, 1)
return
END
UPDATE address
, description = inserted.description
, customerid = inserted.customerid
, housenumber = inserted.housenumber
, prefix = inserted.prefix
, street = inserted.street
, suffix = inserted.suffix
, apartment = inserted.apartment
, city = inserted.city
, state = inserted.state
, zip = inserted.zip
, zone = inserted.zone
, businessid = inserted.businessid
, active = inserted.active
, contact = inserted.contact
FROM inserted
where address.id = inserted.id
ANDinserted.id 0
end
SQL = Scarcely Qualifies as a Language
December 10, 2005 at 1:42 pm
OK, thanks. One other question. Should I do this for every field in the table that might be updated because at times, only the address name or the zone would be updated and other times all of it would be updated. What happens if a statement like this is executed by the application:
Update Address Set AddressName = 'Home' Where ID = 27896
Would I still be able to do those other fields or do I now have a problem because they are empty?
December 10, 2005 at 4:50 pm
The code Carl posted will handle all values of ID just fine if you're only inserting one row at a time. If you just don't want 0 values to insert, you don't even have to do a status check... just use the update statement Carl provided as the body of your trigger.
K. Brian Kelley
@kbriankelley
December 11, 2005 at 1:48 am
Thanks, but I still have the question as follows.
Let's say there is no other value being inserted but the description field. If I use this statement:
UPDATE address
, description = inserted.description
, customerid = inserted.customerid
, housenumber = inserted.housenumber
, prefix = inserted.prefix
, street = inserted.street
, suffix = inserted.suffix
, apartment = inserted.apartment
, city = inserted.city
, state = inserted.state
, zip = inserted.zip
, zone = inserted.zone
, businessid = inserted.businessid
, active = inserted.active
, contact = inserted.contact
FROM inserted
where address.id = inserted.id
AND inserted.id <> 0
What will happen to housenumber if inserted.housenumber doesn't exist? What will happen to street if inserted.street doesn't exist? Etc....?
Thanks!
Mike
December 11, 2005 at 7:19 am
All columns exist. The inserted tablespace shows up on INSERT and UPDATE operations. It contains the rowsets that are changing as they would be AFTER the operation. The deleted tablespace shows up on DELETE and UPDATE operations. It contains the rowsets that are changing as they exist BEFORE the operation. These two special tablespaces contain all rows that are changing and contain all columns of each of these rows. For all purposes, consider them tables.
The one exception would be in normal trigger (AFTER triggers) in which case you can't work with text columns. However, in INSTEAD of triggers, everything is present.
K. Brian Kelley
@kbriankelley
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply