Re: Record level security?

Is "deleted d" referring to the table that the trigger is being created
on?...or is it necessary syntax as is? I'm trying to slightly adjust the
code to work with our real table names and fields and I have the RAISERROR
error working on the insert and update action but seems like the trigger is
not sensing the delete action and it is allowing it to delete a test record
which is suppose to be frozen.

This trigger lock method is going to save the day in freezing our mission
critical data. Thanks so much Erland.




"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message
news:Xns9A2AF8EF3E5Yazorman@xxxxxxxxxxxx
zz12 (IDontLikeSpam@xxxxxxxxxxx) writes:
Yes actually I have another table I'll refer to as [tblReadyToLock] that
holds 3 columns (FiscalYear, Quarter, Ready) which it can use to check on
with values like: 2008, 2, 'Yes'

Would you happen to know the syntax that would accommodate this for me?

IF EXISTS (SELECT *
FROM deleted d
JOIN tblReadyToLock L ON year(d.somedate) = L.FiscalYear
AND datepart(q, d.somedate) = L.Quarter
WHERE L.Ready = 'Yes')

I did not recall the code for quarter in Datepart, so I had to guess.
Look it up in Books Online if it does not work.



--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


.

Relevant Pages

  • Re: Record level security?
    ... the trigger is not sensing the delete action and it is allowing it to ... as prefixes; using the table names as prefixes causes code clutter. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.security)
  • Re: Add a datestamp to an existing table
    ... Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books ... ... Are there examples of creating a trigger for tracking table data ... If you choose a design with a seperate history table that holds all ...
    (comp.databases.ms-sqlserver)
  • Re: Record level security?
    ... and then another one that lists ... I would probably do it in one trigger, but I grow up in a cruel world ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.security)
  • Re: History/audit without history tables
    ... I would typically do this in a trigger. ... JOIN pledgegroupmembership pgm ON i.accno = pgm.accno ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Trigger to populate table or database
    ... I figured it out, I got the error trying to make a new trigger, but I ... time ago and I believe you got me on the path to Linked Servers. ... There is no error number 31037 in SQL Server, ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)