Re: Record level security?

Very cool.

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?

Thanks a bunch Erland this appears to be exactly what I need.


"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message
news:Xns9A292347CAEAYazorman@xxxxxxxxxxxx
zz12 (IDontLikeSpam@xxxxxxxxxxx) writes:
Hello, is it possible to not allow changes to certain records in a sql2k
user table? We have a table that holds data for a periodic report and
was wondering if it would be possible to not allow any changes/deletions
to certain records that fall within a certain date period criteria as to
where this 'historic' data can't be altered.

CREATE TRIGGER ajabaja ON tbl FOR UPDATE, DELETE AS

IF EXISTS (SELECT *
FROM deleted
WHERE somedatecol BETWEEN '20070101' AND '20071231')
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('Data is frozen and may not be deleted or altered', 16, 1)
RETURN
END

You probably need to find a way for the trigger to found how which is
the forbidden period. For instance you could read it from a table.

--
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?
    ... We have a table that holds data for a periodic report and ... CREATE TRIGGER ajabaja ON tbl FOR UPDATE, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.security)
  • Re: Record level security?
    ... Thanks a bunch Erland this appears to be exactly what I need. ... CREATE TRIGGER ajabaja ON tbl FOR UPDATE, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.security)
  • Re: Query training -- Complex queries
    ... > Update PositionsEOM ... Here you don't have a derived table, but a correlated subquery. ... UPDATE in Books Online. ... is not written for SQL Server. ...
    (microsoft.public.sqlserver.server)
  • Re: STORED PROCEDURE - passing table name as a parameter
    ... T-SQL, is that you get problems if the view definition does not fit into ... will need to query other system tables, for instance syscolumns. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Problem when changing Views
    ... There aree several reasons. ... I use neither of the tools, but I guess that Design View resubmits the ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)