Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

Instead of trigger and table valued function

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
Choran6619
Asked:
Choran6619
  • 3
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>"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
 
Choran6619Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you show the TVF anyhow, please?
+ the script to create the table + view ... to reproduce the problem here easily
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
Choran6619Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
glad you found your solution
0
 
Choran6619Author Commented:
I prefer solutions that solve the issue..rather than simply restating the error message.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now