Click here to Skip to main content
15,121,678 members
Please Sign up or sign in to vote.
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]
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

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!
"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
piushshukla 6-Mar-12 8:50am
I want to keep change record in single 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.

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.


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