-3

Possible Duplicate:
SQL Server history table - populate through SP or Trigger?

I am using this trigger

CREATE TRIGGER [dbo].[Band_Level_trg] 
-- ALTER TRIGGER [dbo].[Test_PTA_Table_Update_trg] 
   ON  [dbo].[Band_Level] 
   INSTEAD OF UPDATE
AS 
   SET NOCOUNT ON
   DECLARE @key int
   SET @key = (SELECT band_level_id FROM Inserted)

   UPDATE Band_Level
      SET band_level_name = band_level_name, description = description
      WHERE band_level_id = @key 

   INSERT INTO dbo.Band_Level
   (band_level_name, description)
   (SELECT band_level_name,description 
      FROM Inserted)

but i want to show history on another page.it shows history on same page

Community
  • 1
  • 1
Piush shukla
  • 165
  • 1
  • 4
  • 16

1 Answers1

1

Given this other question you posted:

maintain history through trigger in asp.net (which sorry to say, is also horribly explained)

I think I figured out what you want to do. You want to keep a "history" of the changes using this trigger. Also, what I figured is that, you are "showing the history in the same page" because the trigger inserts on the same table you're updating! The purpose of the history is to do it on ANOTHER table, if not, your history will become actual data of the table you wanna keep a history of.

You should create another table with the same columns and change the trigger accordingly. Just create a Band_Level_History table and change the trigger to save the changes there. That's it. Like this:

Instead of: 

INSERT INTO dbo.Band_Level

Put:

INSERT INTO dbo.Band_Level_History

Also, I'd restructure the triggers in a different way. You should REALLY read this article:

https://web.archive.org/web/20210608144836/http://www.4guysfromrolla.com/webtech/091901-1.shtml

It's short so please read it. Also, next time please try to explain things a little better so everyone can understand. I'm aware that you probably have a language barrier but that's OK. Just try and do your best, I'm from Argentina and I can assure you: eventually you'll learn English if you are consistently trying.

Hope this helps

Community
  • 1
  • 1
Gaspa79
  • 5,488
  • 4
  • 40
  • 63
  • 1
    It is also important in writing triggers to ensure that multi record inserts/updates/deletes are handled. Virtually any SQL server trigger that sets the results from inserted or deleted to a scalar variable is not going to work correctly because these tables can have more than one record. – HLGEM Feb 22 '12 at 14:59
  • Completely true! That's why I said that I'd do the trigger in a different way. The whole "selecting the ID" thing didn't convince me. – Gaspa79 Feb 22 '12 at 15:02