Post reply

help me in delete records

  • August 27, 2005 at 5:41 am

    #585670

    CREATE TRIGGER trgInsertIntoNewTable

    ON test2

    FOR DELETE

    AS

    insert INTO dbo.test3 (columns list) Select ColumnsList FROM Inserted

    GO

  • August 27, 2005 at 8:41 am

    #585671

    remi - not sure why you changed it to "FROM Inserted"...

    shouldn't it still be...

    CREATE TRIGGER trgInsertIntoNewTable
ON test2
FOR DELETE
AS

Insert INTO dbo.test3 (columns list) Select ColumnsList FROM Deleted

GO

    smruti - I have a feeling that where you may be going wrong is not using the word DELETED in your trigger - you see - when rows are either inserted, updated or deleted from a table, sql server creates two "internal tables" called INSERTED & DELETED....when you delete from your test2 table, all the rows are now available in a table called DELETED - so you insert into your test3 table FROM DELETED...

    hope this gets resolved now...







    **ASCII stupid question, get a stupid ANSI !!!**

  • August 27, 2005 at 10:54 pm

    #585691

    My bad...

  • August 27, 2005 at 10:56 pm

    #585692

    OMG rg - where did you learn to speak "American" ?!?!







    **ASCII stupid question, get a stupid ANSI !!!**

  • August 27, 2005 at 10:59 pm

    #585693

    Fox 44.

  • August 28, 2005 at 8:45 pm

    #585734

    you can use trigger. Trigger will create automatically 2 table inserted and deleted. When you delete, from deleted table you can insert those records got deleted to the new table as you wish.

  • August 29, 2005 at 4:49 am

    #585773

    Hi sushila

    Than'x Ur code working properly

    But ont thing I have to follow and is that

    If i execute this query I am getting the inserted records in that table for once only and again we have to execute the trigger by giving another name

    but

    My question was that

    Suppose accidentaly I execute the delete code on any table the table value must be inserted into another table.

    And ur sol is to execute the trigger only

    it may be that u didn't got me

    Let in query analyser I execute the follwing query

    *********

    Delete table1

    ******

    So this above query will delete my records so i want that the records of this table must be inserted into another table whenever I will execute this query.So in this situation the table name may be varry.

    So for the security purpose I want that if incidently It happens how to retrive the deleted records. You  may take the backups but that should be automatically.

    If u have got me then thanks

    since i am new in sql server so this is my problem.

    As One day I had deleted my table

    So for that purpose I want that If anytime i will

    delete any table those records of that table must be inserted into new table

    Than'x a lot that u have tried so much

    So If u can try on this then It will be helpful to me

    Than'x again

     

     

     

     

  • August 29, 2005 at 5:42 am

    #585775

    You may take the backups but that should be automatically.....

    Smruti - the only way you can safeguard database/yourself against accidental deletes is by having a backup of the database - not sure if I understand what you mean by..."that should be done automatically" - you or someone else will have to create a (preferably scheduled) backup job!!!

    I don't know what kind of access permissions you have to the database, but my suggestion would be to ask your DBA to explicitly DENY your account any permissions to DELETE any of the database objects...or at least the tables that should not be deleted...please read up on granting and denying permissions on BOL (books online)...that way you have a failsafe way of guarding yourself against accidents...

    Otherwise you are going to have twice the number of tables you currently have in the database if for each table you have, you want to create a "table copy" that will store any accidental deletes...and you will have to set a trigger on each one of these...this is NOT a solution at all..







    **ASCII stupid question, get a stupid ANSI !!!**

Viewing 8 posts - 31 through 37 (of 37 total)

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