Solved

Instead of trigger and table valued function

Posted on 2010-08-31
6
364 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
  • 3
  • 3
6 Comments
 
LVL 142

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 142

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 142

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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 tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

867 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now