Solved

Instead of trigger and table valued function

Posted on 2010-08-31
6
371 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

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