Click here to Skip to main content
15,074,668 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Dear,

I am developing a web application with C #, where I want right now is to resolve this situation:

I have a table X that containing some columns and i have another table Y that is a mirror of X to audit. what I want is when I do an update table X, the data that this table was altered before being copied to the table Y.

I intend to do this with a trigger but do not know how to solve. someone to give me some pointers?

My structure is as follows:
SQL
table_X(
id_X int primarykey identity
nome varchar(200)
apelido varchar(200)
empresa varchar(200)
data_registo datetime
nro_cliente int
)


table_Y(
id_audit int primarykey identity
nome varchar(200)
apelido varchar(200)
empresa varchar(200)
data_registo datetime
nro_cliente int
)


Initially, I have a StoredProcedure ready to do the update in table X, where I spend all affected fields and do the update. but I want to enter the initial data in the table Y, before the data are updated in X.
Posted
Updated 31-Jul-13 3:58am
v3
Comments
Maciej Los 31-Jul-13 9:34am
   
And... Where are you stuck?

I have no idea where are you stuck... And why... But this could be done in 2 simple steps:
1) insert data into Y table based on select from X
2) update X

SQL
CREATE PROCEDURE usp_UpdateAndAudit
    @variable1 type,
    @variable2 type,
    .....
    @variableN type
AS
BEGIN
    --step 1)
    INSERT INTO Y (DestField1, DestField2,..., DestFieldN)
    SELECT SrcField1, SrcField2,..., SrcFieldN
    FROM Y
    WHERE <condition>


    --step 2)
    UPDATE X
       SET Field1 = @variable1
       SET Field2 = @variable1
       SET FieldN = @variableN
   WHERE <condition>
END
   
v2
Comments
Adarsh chauhan 1-Aug-13 1:42am
   
I agree.. this can be done in the same stored procedure, no need of a trigger..
first insert records from table X to table Y then update table x.. simple... :)
+5
Maciej Los 1-Aug-13 1:44am
   
Thank you, Adarsh ;)
Adarsh chauhan 1-Aug-13 1:47am
   
Hey I have question. I am using SSMSEE 2008 but shortcuts aren't working( for eg. ctrl+n) only F5 is working. Is there any way to make them work??
Maciej Los 1-Aug-13 1:53am
   
My old friend - very good IT trainer says: Try to reinstall software, which wouldn't work ;)
Adarsh chauhan 1-Aug-13 2:02am
   
ya but it will require more time.. I am asking because some shortcuts are working.
so I thought may be there is any option to make all work..OK, I will reinstall it once I am done with my current project. Till then will manage without shortcuts.. :)
Maciej Los 1-Aug-13 2:20am
   
Seriously, go to: Tools->Options. In "Options" window on the left side choose: Environment->Keyboard and click "Reset to default" button ;)
Maciej Los 1-Aug-13 2:23am
   
Another way is to use Tools->Cutomize and then in a "Toolbars" tab click "Reset" button.
Adarsh chauhan 1-Aug-13 2:23am
   
Thanks.. :)
Maciej Los 1-Aug-13 2:24am
   
You're welcome ;)
It sounds as though you have no idea how to create a Trigger. I suggest reading up on them from these[^].

Oh, and this has nothing to do with C# or ASP.NET.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900