Solved

Instead of trigger and table valued function

Posted on 2010-08-31
6
375 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

635 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