Re: Record level security?

zz12 (IDontLikeSpam@xxxxxxxxxxx) writes:
I tried creating only 1 trigger that lists "FOR INSERT, UPDATE, DELETE"
but it didn't seem to fully work in keeping the data totally frozen.
But I think I have this working now in that I ended up creating 2
triggers on the targeted frozen historic table. I created one that
lists:
"FOR INSERT, UPDATE" and using the "FROM inserted i" as the alias

and then another one that lists
"FOR UPDATE, DELETE" and using the "FROM deleted d" as the alias.

I guess that if it works, it works.

I interpreted your question that you did not want existing rows to
be deleted or changed. But it seems logical that you also want to
avoid new rows to be added, or existing rows to be moved into the
frozen period.

I'm not sure if this is the normal way of doing it but I'm really glad
it's working.

I would probably do it in one trigger, but I grow up in a cruel world
where you could only have one trigger per action on a table, so I had to
learn how to do it. :-)

--
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: Portal Server, WSS List Update/Insert Error
    ... trigger into the PORTAL_SITE database. ... What happened was we grew the PORTAL_SITE Database to 100 gigs for growth ... SQL Server Error ... I can still upload documents, create new lists, and edit portal areas and ...
    (microsoft.public.sharepoint.portalserver)
  • 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: Auditing and IP addresses
    ... Create a certificate and which you sign the trigger with. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.security)