August 2, 2005 at 1:07 am
I was hoping someone could explain behavior I am seeing:
I have a view which is a join on two tables, I set up an INSTEAD OF UPDATE trigger which will permit a user to update data in the first of the two tables. The trigger does the update and saves a copy of the changed records in the first table to an audit table.
When I then use a table display of the view and change data in either table, it is updated. The audit table is not touched. If I use a T-SQL update statement, I get what I expect out of the trigger. If I set up a second AFTER UPDATE trigger on the first table to save the audit records, it works.
There was no mention of the second table in the update trigger.
I thought an INSTEAD OF UPDATE trigger would not permit this kind of behavior.
Thanks for the help, Mike
August 3, 2005 at 4:33 am
Do you mean your INSTEAD OF UPDATE is not firing when you edit the data in the view though Enterprise Manager - Open Table / Query Analyzer Object Browser - Open?
Could it not be that you accidentally opened the tables instead of the view?
August 4, 2005 at 12:02 am
No, I was very carefull. The INSTEAD OF UPDATE seems to be firing, bu it has behavior that is not included in the trigger definition. It will allow updates of the second table in the view which is not mentioned in the trigger.
If you are interested I can post an example of this using the pubs database.
August 4, 2005 at 1:52 am
Here is a simple example using the pubs db ...
create trigger test1_up on test1 instead of update as
BEGIN
update stores set stores.city = i.city, stores.state = i.state,
stores.stor_id = i.stor_id
from inserted i where stores.stor_id = i.stor_id
insert into stores_d (stor_id, city, state, dateChanged)
select d.stor_id, d.city, d.state, getdate() as dateDeleted from deleted d
END
create view test1 as
select stores.stor_id, stores.city, stores.state, sales.qty, sales.payterms, sales.title_id
from stores inner join sales on stores.stor_id = sales.stor_id
CREATE TABLE [stores_d] (
[stor_id] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[city] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[state] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dateDeleted] [datetime] NULL CONSTRAINT [DF_stores_d_DateDeleted] DEFAULT (getdate())
) ON [PRIMARY]
GO
CREATE TABLE [sales_d] (
[stor_id] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ord_num] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ord_date] [datetime] NOT NULL ,
[qty] [smallint] NOT NULL ,
[payterms] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[title_id] [tid] NOT NULL ,
[DateDeleted] [datetime] NULL CONSTRAINT [DF_sales_d_DateDeleted] DEFAULT (getdate())
) ON [PRIMARY]
GO
Then open the view using Enterprise Manager and change some of the values.
If you execute a simple Update on the view from Query Analyser, you get something different.
Then go look in the stores, sales, sales_d and stores_d tables to look at the results.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply