Trigger on a View in SQL 2000
-
Hi,
I am trying to add a trigger to a view in SQL 2000. I previously had the trigger working on the underlying table, but now need to put it on the view. I found out that AFFTER triggers are not supported by views and that I need to use the INSTEAD-OF trigger. I used the following code and Query analyser created the trigger, but the trigger does not insert into the specified table on update.
CREATE TRIGGER [TR_AUDIT_CONTACT_U] ON dbo.VW_ContactsNotDeleted
INSTEAD OF UPDATE
AS
DECLARE @RecordKey int,@ContactKey int
SET @RecordKey = (SELECT ContactID FROM INSERTED)
SET @ContactKey = (SELECT ContactID FROM INSERTED)
INSERT INTO TBL_APP_AUDITLOG (UserName,ActionDate,TableID,ActionName,RecordKey,ContactKey) VALUES(SYSTEM_USER,getDate(),1,'UPDATE',@RecordKey,@ContactKey)
Also, now that this trigger is instead of do I need to add another sql statement to manually update the base table?
Thanks
Chris
-
Chris,
Is the trigger being fired? Does the view meet the requirements for INSTEAD OF triggers?
From the looks of your code, it doesn't cater for multi-row updates, so any update that affects more than 1 row will probably crash when it hits your SET statement.
Another thing you might want to check (although it won't affect whether your trigger works or not) is that you're assigning ContactID to both of your declared variables.
And, yes, you do have to explicitly update the base table in your trigger.
Cheers,
- Mark
Cheers,
- Mark -
Hi Mark,
Thanks for replying. As far as I know the view should meet the requirements, but I will have a good hard look in BOL to check this one out. Where you say 'Is the trigger being fired?' How can I check this? I know that the action specified in the trigger is not being performed, but is there another way to see that the trigger fired or not?
It should be OK that it doesn't cater for multi-row as the front-end application will not allow this option to the user, and they won't be getting SQL client tools installed.
There is a reason why there are 2 ContactID parameters as this saves me from needing to make a join in another query. This is just 1 of many triggers and it just happens that in this 1 the two values are the same.
Thanks
Chris
-
Chris,
I know you said you don't need to, but I can't let go of this one
... You can make the code cater for both single- and multi-row updates with something along the lines of:
ALTER TRIGGER [TR_AUDIT_CONTACT_U] ON dbo.VW_ContactsNotDeleted
INSTEAD OF UPDATE
AS
INSERT INTO TBL_APP_AUDITLOG (UserName,ActionDate,TableID,ActionName,RecordKey,ContactKey)
SELECT SYSTEM_USER,getDate(),1,'UPDATE',ContactID,ContactID FROM INSERTEDThe reason I persist is that your original code will fire, and insert an audit row EVEN if no rows are affected by the UPDATE.
You can temporarily add some RAISERRORs to track activity in the trigger too:
ALTER TRIGGER [TR_AUDIT_CONTACT_U] ON dbo.VW_ContactsNotDeleted
INSTEAD OF UPDATE
AS
RAISERROR('Running the TR_AUDIT_CONTACT_U Trigger. About to create AUDITLOG entries',0,1) WITH NOWAIT
INSERT INTO TBL_APP_AUDITLOG (UserName,ActionDate,TableID,ActionName,RecordKey,ContactKey)
SELECT SYSTEM_USER,getDate(),1,'UPDATE',ContactID,ContactID FROM INSERTED
RAISERROR('AUDITLOG entries created=%d. Error code=%d',0,1, @@ROWCOUNT, @@ERROR) WITH NOWAITOr, you can even use the WITH NOWAIT, LOG option of RAISERROR to direct those messages to the SQL Error Log. Once again, I suggest only doing so as a temporary debugging measure.
Cheers,
- Mark
Cheers,
- Mark -
Hi Mark,
Thanks for your help. I prefer your way of writting triggers without the set commands, I will write all my triggers this way from now on.
I tried your code in my trigger, and it looks to work in SQL client tools, but I think I might have been on a bit of a wild goose chase because I now think it could be something in my Access(.adp) front-end that is not using the SQL views correctly. Although access changes the data correctly in the base table, and does have the view specified as it's datasource it appears to be operating on the base table, or at least bypassing the trigger on the view.
Anybody know a good MS Access board?
Thanks again
Chris
-
Hi Chris,
quote:
Anybody know a good MS Access board?you mean apart from this forum?
If newsgroups are an alternative for you, M$ maintains several newsgroups which I heard should be really good. But not for me, newsgroup access is not allowed here.
http://www.dbforums.com has a section about Access. No idea about the quality.
I guess there are many out there, but can't tell if you will receive a qualified answer, if at all. I would try to ask your question here first. I think right now we've always find an acceptable solution.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
One way to confirm your thinking on the Access front end is to REVOKE or DENY any privileges on the base table to their login. Just GRANT privileges on the view.
Cheers,
- Mark
Cheers,
- Mark -
Hi Guys,
This site is always my first port of call 🙂 It could do with a section just for Access frontend apps tho'. In fact I think I will go and suggest this in the Suggestions forum. I use the MS forums too, and there are some clever people(probably including MS employees) that answer questions, but sometime the questions no-one knows just get left unanswered. There are suposed to be MSDN subscriber only groups somewhere that guarentee a response within 3 days, but I have yet to find them.
I posted this problem there and someone has answered saying they think it is ADO that is the problem. I'm going to look into it.
Failing that I'll try the grant/revoke idea when I am able to get other user logins to work with my database.
Oh, by the way, the MS groups can be accessed online without a newsreader here:
http://support.microsoft.com/newsgroups/
Thanks
Chris
... You can make the code cater for both single- and multi-row updates with something along the lines of:
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply