Post reply

date ( check constraints what ever)Problem in SQL-SERVER 2000

  • May 13, 2004 at 12:34 am

    #108247

    i have a column isDeleted in my particular Table, and i have one column DeletedDate, besides that column, now when

    the user deletes the data doesnot get Deleted instead

    IsDeleted Column is set to 'Y', now what i want something which will check the IsDeleted column for 'Y' and if it finds DeltedDate column should be filled with the date value,

    Got me ??, now how do i do this. ?

    Thanks In Advance

    <a href="http://www.websolsoftware.com"> For IT jobs click here</a>

    *Sukhoi*[font="Arial Narrow"][/font]

  • May 13, 2004 at 12:44 am

    #506239

    Two ways spring to mind.

    1) When the application sets the deleted flag to 'Y', why doesn't it set the deleted date as well as part of the same transaction?  This is the best solution, but another way ...

    2) Create a trigger that runs on update of the table, checking the value of the IsDeleted field.  If the field is 'Y' and DeletedDate is blank, set DeletedDate to GetDate().

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • May 13, 2004 at 2:33 am

    #506250 

    CREATE TABLE YourTable (
 YourID int IDENTITY PRIMARY KEY,
 Something varchar(50) NOT NULL UNIQUE,
 IsDeleted char(1) NOT NULL DEFAULT ('N') CHECK (IsDeleted='N' or IsDeleted='Y'),
 DateDeleted datetime NULL
)
GO
INSERT INTO YourTable(Something) VALUES ('Razvan')
INSERT INTO YourTable(Something) VALUES ('Sukhoi')
GO
    CREATE TRIGGER YourTable_U_Set_DateDeleted ON dbo.YourTable
FOR UPDATE AS
IF UPDATE(IsDeleted) BEGIN
     SET NOCOUNT ON
 
 UPDATE YourTable SET DateDeleted=GETDATE()
 FROM YourTable t INNER JOIN inserted i ON t.YourID=i.YourID
 INNER JOIN deleted d ON t.YourID=d.YourID
 WHERE i.IsDeleted='Y' AND d.IsDeleted='N'
    END
    GO
UPDATE YourTable SET IsDeleted='Y' WHERE Something='Razvan'
GO
SELECT * FROM YourTable

    Razvan

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply