Solved

Instead of trigger and table valued function

Posted on 2010-08-31
6
373 Views
Last Modified: 2013-11-07
I am attempting to create a "read" trigger.  My client has an older system that uses select statements to validate userid and passwords.  Now they want to be able to implement an automated lockout after X failed attempts in Y minutes.

My current approach is ot use view based on a table value function to replace the original users table.

The TVF works fine and the account lockout (using a secondary non-enlisted connection) also works fine.

Unfortunately as soon as I starting working on the instead of triggers for update and delete I receive errors.

If I can solve one error the same solution will most likely solve the others as well...

The first error is:
A .NET Framework error occurred during execution of user-defined routine or aggregate "TestUserTable":
"Invalid use of side-effecting or time-dependent operator in 'UPDATE' within a function."

I recieve this when I attempt to execute the following sql:
update usertable set manager='fubar' where loginid='Chris'

at this point in time the update instead of trigger is defined as follows:

create trigger tr_vwUserTable on UserTable
Instead of update
as
begin
 --do nothing for now
   update usertable_old set manager='fubar'

end

My understanding is that instead of triggers execute...well instead of the action...so why am I receiving this obscure error?
0
Comment
Question by:Choran6619
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 33569690
>"Invalid use of side-effecting or time-dependent operator in 'UPDATE' within a function.

you cannot use UPDATE, INSERT, DELETE within a function.
exception is when those statements refer to a table variable created within the function.
0
 

Author Comment

by:Choran6619
ID: 33570364
I must not have done a god job explaining the issue:

The select should use the TVF.
The instead of trigger (update or delete) should use the original physical table.


The view is based on the TVF.
the triggers are based on the view.

since the triggers are "Instead of" triggers the update statement should not involve the TVF at all.....
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33574594
can you show the TVF anyhow, please?
+ the script to create the table + view ... to reproduce the problem here easily
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Choran6619
ID: 33576834
I'd be more than happy to send you the files, offline, if you have an email that I can use.

I understand why the update is not supported..it requires a delete followed by an insert and the TVF does not support delete.  Without that support the deleted pseudo table can't be build.

Instead I have rolled a different implementation using SQLTracer that is working like a champ.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33576926
glad you found your solution
0
 

Author Closing Comment

by:Choran6619
ID: 33581218
I prefer solutions that solve the issue..rather than simply restating the error message.
0

Featured Post

Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question