Audit a view using a trigger (code inc.)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Audit a view using a trigger (code inc.)

Author

Topic

uberman
Posting Yak Master

159 Posts

Posted - 2006-11-10 : 07:51:33

I need to create an audit trail of changes to stock availablitity through time. Stock is incredibly complex and is made up of physical stock, virtual stock, stock available to order, minimum allowed level, minimum order quantity etc. etc. etc.

We have a nice (although complex) view that provides us with all the information we need regarding the various stock values for all items in ours system.

We now need to audit this, which is basically a copy of the data at change time with a time logged

Below is a gross simplification of the issue only dealing with virtual stock.

If you run the code as is, you should end up with

2006-11-10 12:37:21.010	1	100
2006-11-10 12:37:21.100	1	90
2006-11-10 12:37:21.110	1	75

output.

BUT, I have had to put the trigger on the orders table.

I want to to have the trigger on the view (because the view I want to use amalgamates data from all over the place and putting triggers on the individual tables, or amending all the stored procs that affect the stock is going to be a nightmare, esp. as all we want to do is log the view!)

I have started playing with instead of triggers (and they are commented out below) but I can seem to get them to work, I was hoping to create a trigger on the view and just being able to the the "inserted" table to log everything I want instead of having to join out to other tables...

Could someone please point me in the right direction or am I on a hiding to nothing here?


CREATE DATABASE wibble
go

USE wibble
go

create table stock(
	product_id integer
	,stock_quantity integer
)
go

create table orders(
	product_id integer
	,quantity integer
)
go

create view virtual_stock
as
	select
		s.product_id
		,s.stock_quantity - ISNULL(O.ordered,0) as virtual_stock
	from
		stock s
	left outer join (
		select
			product_id
			,sum(quantity) as ordered
		from
			orders
		group by
			product_id
	) as o
	on
		o.product_id = s.product_id

go

create table virtual_stock_audit(
	logged datetime default getdate()
	,product_id integer
	,virtual_stock integer
)
go

insert into stock
select 1,100
insert into virtual_stock_audit(
	product_id
	,virtual_stock
)
select 1,100
go

select * from virtual_stock
go

--TRIGGER ON ORDERS
create trigger t_audit
on
--virtual_stock
	orders

after
--	instead of

	insert, update
as
	insert into virtual_stock_audit(
		product_id
		,virtual_stock
	)
	select
		i.product_id
		,vs.virtual_stock	
	from
		inserted i
	inner join
		virtual_stock vs
	on
		vs.product_id = i.product_id

go

insert into orders
select 1,10
go

insert into orders
select 1,15
go

go

select * from virtual_stock_audit
go


USE [master]
go

DROP DATABASE wibble
go

uberman
Posting Yak Master

159 Posts

Posted - 2006-11-10 : 09:53:57

I think I am on a hiding to nothing, BoL states that you cant use an after trigger on a view and using instead of will intercept the updates that amended the view, and I dont want to do that, I want to just log changes that are visible in the view..., Plus the "instead of" trigger only fires when the view is included in the update statement and I want to trap when the data that the view amalgamates actually changes (treating the view as table as it were)

ho hum...

It looks like I may have to implement Plan B

Plan B = Invent Plan C

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-10 : 11:02:28

What you are trying to do is simply not possible because in its core a view is just a query, not the table. So, the changes to the view can set off trigger because changes takes place in the underlying tables, not in the view. View is just a snapshot. So whatever trigger you want to write, you have to write it on underlying tables.

Harsh Athalye
India.
"Nothing is Impossible"

Kristen
Test

22859 Posts

Posted - 2006-11-10 : 15:20:31

You could write a trigger on [each of] the underlying table, but then use the VIEW on the records in INSERTED within the trigger (i.e. the records that have changed) and then ONLY record data for products where the stock level, as reported by the View, has changed.

So several triggers on different tables would trigger the checking of stock level using the View, but not all of them would actually find any stocklevel change.

Kristen

uberman
Posting Yak Master

159 Posts

Posted - 2006-11-13 : 06:03:05

Cheers for that harsh/Kristen, that's what Plan C is evolving into, either triggering off the tables that change, or adding a "log product x" call to calling stored procs...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources