trigger in sql to store change history

Question

0.00/5 (No votes)

See more:

I want to create trigger which contain the history of updation

<pre lang="SQL">Alter TRIGGER bandlevel_Trg_Upd ON [dbo].[Employee]
FOR UPDATE
AS
INSERT [dbo].[Test] (emp_id,emp_code,emp_pan_card,emp_gender,GETDATE(),status)
SELECT emp_id,emp_code,emp_pan_card,emp_gender,,=Date 'BEFORE UPDATE'
FROM deleted

INSERT [dbo].[Test] (emp_id,emp_code,emp_pan_card,emp_gender,status,GETDATE())
SELECT emp_id,emp_code,emp_pan_card,emp_gender,Date, 'AFTER UPDATE'
FROM inserted</pre>

but in this trigger it create two table. iwant in single table and show the detail of table which change

Posted 6-Mar-12 2:22am

piushshukla

Add a Solution

2 solutions

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)

E.F. Nijboer · Answer 1 · 2012-03-06T02:32:00

Solution 1

I don't fully understand. What do you mean by "create two table"? deleted and inserted aren't actual tables in this context. They exists as temporary which hold the deleted and inserted rows. Hope this helps, otherwise please elaborate.

Good luck!

Posted 6-Mar-12 2:32am

E.F. Nijboer

Mahmud Hasan · Answer 2 · 2012-03-06T02:37:00

Solution 2

"but in this trigger it create two table. iwant in single table and show the detail of table which change" This line is not clear buddy. I can see you are inserting your previous data and new data in a table named test. But if you record all the insert/update operation you do not need previous data isn't it? Cause you always have the snapshot of your data for every change and thus you can easily trace the change.

Can you give more clarification what exactly you want to do?

You may like to review one tip that I posted earlier on trigger which may help you to refresh your concept.

Be Very Careful When You Write SQL Trigger

Posted 6-Mar-12 2:37am

Mahmud Hasan

Comments

piushshukla 6-Mar-12 8:50am

I want to keep change record in single table.now i created two table but i want to do this on single table.

Mahmud Hasan 6-Mar-12 22:43pm

For this purpose what I do is:
1. I create a table with same fields like the main table for keeping history.
2. Say I am saving a task where I have a table claled Task with fields Id, Name, Description,Status.
3. I will have another table name TaskHistory where I will have fields like Id, TaskId, Name, Description, Status, IsCurrent.
4. Whenever a task will be created an entry will also go to the history table. here isCurrent will be set to true.
5. When the task will be edited, it will be edited in the main table and a new row will get inserted into the history table. Make the previous row's isCurrent false and the new row's is Current true.
6. Thus you have the full changes of a task tracked in the history table.

Another thing, I would like to do this from a post save event of task rather than doing in trigger. Triggers should always be our last option to use. Because, it can create bugs that tough to debug, can create hidden performance problem etc.

Please response if this make sense.

Thanks
Mahmud

piushshukla 7-Mar-12 0:10am

Yes Mahmud

piushshukla 7-Mar-12 0:14am

But Can u give me exact idea so i can do this..

Mahmud Hasan 7-Mar-12 1:24am

Exact Idea? Can you please tell little more how can I help you further. The best way of doing this is to use observer pattern that will store your history on every edit operation. Don't forget to do this under same transaction scope.

Hope to write more if you have specific question.

Please rate my answer if it helps you so that I can be encouraged more.

Thanks
Mahmud