Results 1 to 7 of 7

Thread: Trigger

  1. #1
    Join Date
    Oct 2012
    Posts
    6

    Unanswered: Trigger

    Hi,

    I write a insert trigger on my table LeaveRegister(1000 rows) and inserting rows in audit tabel, but when i inserting a row in LeaveRegister table. In audit table 1000 + 1 rows are inserting every time.

    same try with 1 dummy table instead of LeaveRegister then its working fine.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Perhaps if you posted the code for the trigger, someone might be able to trouble-shoot it?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Aug 2008
    Posts
    147
    use this code for trigger definition:
    SELECT o.[name], c.[text] FROM sys.objects AS o INNER JOIN sys.syscomments AS c ON o.object_id = c.id WHERE o.[type] = 'TR'
    ---------------------------------
    www.sqlserver-dba.com
    www.dba-db2.com

  4. #4
    Join Date
    Oct 2012
    Posts
    6
    Below is the Trigger code

    Create Trigger LeaveRegister
    On ChkLeaveRegister
    For Insert
    As
    Begin
    INSERT INTO Auditlogs
    (Empcode, LeaveCode, ApplyDT, FromDT, ToDT, Status, DateRecorded)
    SELECT Empcode, LeaveCode, ApplyDT, FromDT, ToDT, 'I', getdate()
    FROM ChkLeaveRegister
    END


    If we insert 1 row in chkleaveregister(1000 rows a;ready available in table), in Auditlogs all 1001 rows are insert.

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Don't select from ChkLeaveRegister. Use the special table Inserted, as that contains a copy of the records that have been (you've guessed it) inserted into the target table. The way you've written the trigger, every time you add a record to the table, it will faithfully copy every record in the table to the audit log.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  6. #6
    Join Date
    Oct 2012
    Posts
    6
    Thanks for help.

  7. #7
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    You're welcome!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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