Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2010
    Posts
    153

    Smile Unanswered: delete procedure

    i have a table names as student and another table as student_log.
    When i delete records from table student, those records should get stored in student_log.

    will anyone write a procedure for me?

    structure of the table:-

    name (char(20)) class (int) roll (int)
    -------- -------- ----------
    hari 5 15
    ram 5 20
    ramesh 5 25


    thanks

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Create trigger ... For delete as ...
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by aflorin27 View Post
    Create trigger ... For delete as ...
    Now he can get paid to complete the remaining code.

  4. #4
    Join Date
    Sep 2010
    Posts
    153
    yes, i have already achieved this by trigger. The main problem in using trigger is in bulk deletion or multiple values deletion at one time scenario. Suppose there are 10 records with class 8 and when i delete the records with class 8 then only one record gets stord in the log table.

  5. #5
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    You have to see this: Multirow Considerations for DML Triggers

    Anyway, if both tables have the same structure, the trigger code will be something like:
    INSERT INTO student_log
    SELECT * FROM deleted
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  6. #6
    Join Date
    Sep 2010
    Posts
    153

    Smile

    Even when i am using "deleted" then also i am getting only one row in log table after deleting multiploe data from the main table.


    nyways, thanks a lot

  7. #7
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Quote Originally Posted by sunny_007 View Post
    Even when i am using "deleted" then also i am getting only one row in log table after deleting multiploe data from the main table.
    What???
    Run this example:

    Code:
    CREATE TABLE afl_table1
    (myID integer primary key,
    delID integer,
    name char(10)
    )
    go
    CREATE TABLE afl_log1
    (myID integer primary key,
    delID integer,
    name char(10)
    )
    go
    INSERT into afl_table1 values (1, 1, 'a')
    INSERT into afl_table1 values (2, 1, 'b')
    INSERT into afl_table1 values (3, 2, 'c')
    go
    CREATE TRIGGER trg_del_table1
    on afl_table1
    for delete
    as
    INSERT INTO afl_log1
    SELECT * FROM deleted
    go
    DELETE from afl_table1
    where delID = 1
    go
    SELECT * from afl_table1
    SELECT * from afl_log1
    PS: the above transactions were run in autocommit mode (SET IMPLICIT_TRANSACTIONS OFF)
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by aflorin27 View Post
    Create trigger ... For delete as ...
    Correction:
    Create well written trigger... For delete as

    aflorin, you really need to be more specific in your advice.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •