Allow record to be deleted for 2 min's

[warkre]

I want to allow users to be able to delete a record for 2 or 5 minutes. If they add something completely wrong or twice or so. I already have a trigger on delete that doesn't allow deletion, unless by me. The database is normally accessed through an application over which I have no control. I have a smalldatetime field that must be filled on creation. Should I write a trigger on create or can I do something with default values?
And then someting with datediff I think?
Or something different?
I run SQLExpress 9.0.2047.
-warner-

 

[bborissov]

What is your trigger code? You could check current datetime and see if the record is added withing last 3-5 min.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[warkre]

This is my triggercode:

<code>
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[disable_delete_klanten]
ON [almanak].[dbo].[almanak_klanten]
FOR DELETE
AS
declare @archive int ,
@mod_by_whom varchar(20)

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
select @mod_by_whom = (select kl_laatste_wijz_wie from deleted)
select @archive = (select kl_gearchiveerd from deleted)
IF NOT((@mod_by_whom = 'warner') AND (@archive = '1'))
BEGIN
ROLLBACK

RAISERROR( 'Klantengegevens mogen niet door u worden verwijderd. Warner.',16,1)
END
END
</code>
But where do I find the creation date?

 

[bborissov]

Do not program your code to handle ONLY one record in trigger. What will happens if you want to delete a bunch of records at a time?
I'll check your code now.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[bborissov]

Try this:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[disable_delete_klanten]  
   ON   [almanak].[dbo].[almanak_klanten]
   FOR DELETE
AS
declare @archive      int,
        @mod_by_whom  int,
        @datediff     int
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    -- Check to see if one of these records are modified by someone else than 'warner'
    select     @mod_by_whom  = COUNT(*) FROM deleted WHERE kl_laatste_wijz_wie <> 'warner')
    
    -- Check to see if one of these records are has kl_gearchiveerd     <> '1'
    select     @archive      = COUNT(*) FROM deleted WHERE kl_gearchiveerd     <> '1')
    
    -- Check to see what is the maximum difference between dateTime field and current date
    select     @datediff     = MAX(DateDiff(mi, SmallDateTimeField, GETDATE()) FROM Deleted
    
    IF ((@mod_by_whom <> 0) OR (@archive <> 0)) AND @datediff > 5 -- 5 minutes
        ---  (If some of records are modified by somone else not by warner
        ---  or kl_gearchiveerd have status different by '1')
        ---  AND there is at least onbe record that is stored before 5 minutes
        ---  ROLLBACK transaction 
        BEGIN
           ROLLBACK
           RAISERROR( 'Klantengegevens mogen niet door u worden verwijderd. Warner.',16,1)
        END
END

NOT TESTED AT ALL so be sure you have a very good backup first and made proper tests.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP