Re: Logging deletes...

From: Chris Marsh (cmarsh_at_synergy-intl.com)
Date: 11/12/04 

Date: Fri, 12 Nov 2004 12:43:18 -0500

Tom,

Thanks for the reply. I have tried the following without much success. On
both tables customer and delcustomer I have added a field called
"del_datetime" and then put in the default value "(getdate())" in hopes that
when the customer record is deleted, the trigger is fired and then the row
is added to the delcustomer table it would automatically add the datetime
value.

I am confident that if I get one of those values to update then I can add
the addtional values as well. Here is my current trigger:
_______________________
CREATE TRIGGER [SaveDeletedCustomer] ON [dbo].[customer]
INSTEAD OF DELETE -- Must use INSTEAD OF as the customer table has many Text
columns.
AS

--insert into delcustomer select * from deleted del --Save deleted record in
the Delcustomer table

INSERT INTO delcustomer
SELECT [cusid], ........ I deleted the other fields to make the email more
readable ....[pwd_key], [del_datetime]
FROM deleted del

DELETE customer FROM deleted where customer.cusid = deleted.cusid
_______________________

What am I doing wrong?

Thanks again.

Chris

"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:uaT%23P5MyEHA.924@TK2MSFTNGP10.phx.gbl...
> Check out CURRENT_USER and CURRENT_TIMESTAMP in the BOL.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "Chris Marsh" <cmarsh@synergy-intl.com> wrote in message
> news:OsVx41MyEHA.2568@TK2MSFTNGP11.phx.gbl...
> So now I am cooking on my delete triggers; have them setup on about a
> dozen key tables and they are working like a champ! It would be really
> cool
> to
> know who has deleted the record and log that in the new Delxxxx table that
> I
> created (from the trigger). Or, is
> there another approach that is better?
>
> Any example on how I can log a users delete including the date and time
> within the trigger would be great. It
> would really be amazing if I even knew what PC they did it from?
>
> Each day I work with SQL 2000 I get more and more impressed by it's
> capabilities. I've spent the past 14 years in FoxPro data and there is
> just
> no comparison.
>
> Thanks again!
>
> Chris
>
>

Relevant Pages

  • Re: Triiger -vs- Constraint
    ... An AFTER trigger is almost bound to give worse performance, ... So I would say that constraints in the normal case is faster than triggers. ... customer must belong to a customer category which also is active. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: Tables security
    ... You could put a "do nothing" INSTEAD OF DELETE trigger on it: ... SQL Server MVP ... Columnist, SQL Server Professional ... I created a table, this table's name is Customer, Fields are,CustomerName: Nchar), in SQL Server. ...
    (microsoft.public.sqlserver.security)
  • Re: trigger changing other table
    ... CREATE TRIGGER trigger1 ON customer FOR DELETE AS ... Columnist, SQL Server Professional ... I came here to ask one question, because I have a problem with mssql trigger creation: is it possible to create trigger on table A which modifies some records in table B? ... CREATE TRIGGER trigger1 ON customer FOR DELETE AS UPDATE note SET note.status = 2 WHERE note.customer_id = deleted.id is there any solution of this problem? ...
    (microsoft.public.sqlserver.programming)
  • Re: trigger changing other table
    ... for example when I decide in trigger that his delete operation should be ... Artur ... > CREATE TRIGGER trigger1 ON customer FOR DELETE AS ... > Columnist, SQL Server Professional ...
    (microsoft.public.sqlserver.programming)
  • Re: Logging database changes
    ... I use a "changes" file with a nask key and fields containing record- ... I have a generic trigger subroutine that I clone & modify for each new ... as those equate lists must be managed ... frequently, like customer balance, etc. ...
    (comp.databases.pick)