Creating a Trigger to insert data in another table after the first update..?

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

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 31-Jul-13 3:23am

fasher_the_one

Updated 31-Jul-13 3:58am

Joezer BH

v3

Add a Solution

Comments

Maciej Los 31-Jul-13 9:34am

And... Where are you stuck?

2 solutions

Solution 2

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.

Posted 31-Jul-13 3:40am

Dave Kreskowiak

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2013-07-31T03:40:00

Solution 1

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

Posted 31-Jul-13 3:40am

Maciej Los

Updated 31-Jul-13 3:41am

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 ;)