SQL Server Performance

SQL Store procedure

Discussion in 'SQL Server 2008 General DBA Questions' started by Tashee Duks, Oct 25, 2011.

  1. Tashee Duks New Member

    Hi Everyone,
    My question is, how to use two store statement in one store procedure. Well to explain briefly, I have got two tables (dbo.Customer & dbo.Audit_Customer). The dbo.customer table is a main table to store the customer information. The dbo.Audit_Customer table is for storing each and every record changes made in dbo.Customer.

    For example, if your adds the information into the dbo.customer table, the copy of the record should be record in dbo.Audit_Customer with the status “C”. Likewise, if user deletes the record, the record will be deleted from the dbo.customer but the record which is deleted should be record in the dbo.Audit_Customer with status “D” and so on…

    This process is been designed to monitor the data changes made in the table (dbo.customer).

    Sample Table is attahed. Please see the attachment.

    The indvidual store procedure is working fine but i am not getting an idea to use two statement in one store procedure to get the result which i have attached.

    Please help me.


    Tashi Duks

    Attached Files:

    • sp1.txt
      sp1.txt
      File size:
      1.2 KB
      Views:
      7
    Tashee Duks, Oct 25, 2011
    #1

  2. FrankKalis Moderator

    Welcome to the forum!
    It sounds like you are looking for a trigger to monitor changes and react to them. There are lots of similar examples on the web.
    FrankKalis, Oct 26, 2011
    #2

  3. Tashee Duks New Member

    Thanks for the reply, actually i am looking for stored procedure to trigger from the VB Form. So can you please help me out regarding this.

    Thanks,
    Tashee Duks, Oct 26, 2011
    #3

  4. Shehap Moderator

    It is much preferred to be done through triggers as long as it would be pure DB auditing process for 2 wises :

    · Expatiating more any DML transactions either Update or Insert auditing here would be internal process

    · Making it more productive since if needed to change any business relevant to auditing , it could be done through one or 2 triggers instead of changing all relevant SPs
    Shehap, Oct 28, 2011
    #4

  5. Tashee Duks New Member

    Thanks for your guidence. At last i could get a solution which i am looking for and its working fine. Anyways thanks for your advice.
    Tashee Duks, Oct 28, 2011
    #5

Share This Page