Retrieving old column data in triggers ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Retrieving old column data in triggers ?

Author

Topic

MrBean
Starting Member

14 Posts

Posted - 2001-11-26 : 04:49:36

I developing a system where I need to log changes made to table data using a trigger.

This is the trigger I'm currently using :

CREATE TRIGGER MYDATAUPDATETRIGGER ON [MyData]
FOR UPDATE AS
DECLARE @Now datetime
SELECT @Now = GETDATE()
INSERT INTO
MyDataLog
(MDL_DateTime, MDL_ID, MDL_Operation)
SELECT
@Now, [MD_ID], 2 FROM inserted

But I also need to log the changes made when the MD_ID column is changed in the MyData table, which means I need to log both the new and old MD_ID data.

The trigger above only logs the new MD_ID data but not the old value.

How can I do this ?

/Bean

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2001-11-26 : 07:25:39

Hi

Couple of ideas..

If you are using SQL 2000 you could write an Instead Of Trigger. There is an article round here somewhere about triggers in SQL 2000.

However, if you are using v7....

Seeing as you have the data logged, it would make sense that when it changes, the "old" data will be what you have as the last entry in your log. If you really need to use it again, just grab the last entry for that ID.

Hope that helps

Damian

MrBean
Starting Member

14 Posts

Posted - 2001-11-26 : 07:55:33

Thank you for the suggestions ;-)

I'm using SQL7.

I can't really get the last ID from the log since
the MyDataLog log is truncated once in a while.

But I think I found a workaround :
I simply limit my trigger to log the old MD_ID (from the deleted table) when only one record is updated.
When more than one record is updated, I log NULL as the old MD_ID.
The limitation of this solution is ofcource that it won't log changes made to the NM_ID column when more than one is updated.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2001-11-26 : 08:03:48

Hi

What if, instead of doing a truncate on your lag table, you wrote a procedure that deleted entries where you have two or more of that particular MD_ID, and leave the most recent for that MD_ID. That way, you will always the last value for it.

Does that make sense ?

Damian

MrBean
Starting Member

14 Posts

Posted - 2001-11-26 : 08:44:21

Ok, but how do I find out which record in MyDataLog contains the old MD_ID value when MD_ID is the primary key for the record ?

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2001-11-26 : 17:36:27

Have a date column with GetDate() as a default and sort by that.

Damian

SKIBUM
Starting Member

32 Posts

Posted - 2001-11-26 : 18:24:31

SQL Server has 2 very useful virtual tables named 'inserted' and 'deleted' that can be queryed to get the 'old' data that you just updated or deleted.

For example...here is the code I have in an UPDATE Trigger that updates the 'dateupdated' field for the record being updated...it uses the virutal 'inserted' table

FOR UPDATE
AS

UPDATE USERS_T
SET Last_Update = getdate()
WHERE ((pkUserID IN (SELECT pkUserID FROM inserted)))

I have a feeling that this is where you were going with this question...

Good Luck.

MrBean
Starting Member

14 Posts

Posted - 2001-11-27 : 09:28:53

Thanks guys for the suggestions but I belive the suggestion just won't work if you think it thru !

Remember MD_ID is the primary key for the rows and when there's no way of joining them after it's changed.

Also remember the rows in the "inserted" and "deleted" virtual tabels can contaim multiple rows with different MD_ID rows.

Like this...

inserted :
MD_ID Name
1010 James
1020 Peter
1030 Mary

deleted :
MD_ID Name
10 Mr. James
20 Mr. Peter
30 Mrs. Mary

There's no way to know which row belongs to which row by joining them ;-(

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-11-28 : 13:52:30

MrBean (nice name, by the way)

You have quite a conundrum here, and I don't have the exact solution you initially requested, but rather a different approach. Given that Primary Keys should rarely, if ever change (otherwise they shouldn't be the Key), I would suggest you write a stored procedure to handle this very exceptional event. Inside your stored procedure you can then insert the appropriate log notes for the rows affected.

Presumably you are only updating an occasional row, so this can handle the log events for that one row. On the off chance that you need to do an update to a mass number of primary keys, then you should be able to generate a mass INSERT...SELECT statement inside your stored procedure to log the change just prior to issuing the Update statement (use the same WHERE clause for both).

Again, if you find that your MD_ID (Primary Key) changes frequently, you should consider changing what is used as the Primary Key.

-------------------
It's a SQL thing...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-11-28 : 14:39:28

While I once again agree with Mark 100%, because, well, HE'S RIGHT!, I just thought of a possible solution.

If the MD_ID column does change, you could LEFT JOIN the inserted and deleted tables to find the rows that are different:

SELECT i.* FROM inserted i LEFT JOIN deleted d ON (i.MD_ID=d.MD_ID)
WHERE d.MD_ID Is Null

This query would give you the new rows where MD_ID changed. To get the original rows:

SELECT d.* FROM inserted i RIGHT JOIN deleted d ON (i.MD_ID=d.MD_ID)
WHERE i.MD_ID Is Null

As to how to relate the two together, you'd have to do some extra work to compare the columns between these two queries (making them subqueries and performing extra joins might work).

Since I'm sure you can see that this is a lot of work and may not be successful anyway, you will probably be better off following Mark's suggestion and using some other key, or simply not allowing changes to the current one.

BTW, why are changes to the PK column allowed?

MrBean
Starting Member

14 Posts

Posted - 2001-12-03 : 04:37:29

Thanks for the suggestions guys ;-)

First of all I don't see what's so special about changing the primary key in a table - I have done a few projects allready where the primary key could be changed, and I don't consider those projects to badly designed ;-)
I'm aware that Microsoft like people to use identity columns as primary keys, but I found this solution to be impractical in a lot of cases.

Another thing, in this project, I'm not in control of the design of the table I want to log changes for. It's a Customer table in a ERP system (Navision Financials) where I'm not allowed to change the layout. And the Customer number is the PK which can be changed by the user.

Anyway, I have found a solution which works for me :
When only one row is changed, I log the new and old PK value.
When more than one row is updated (inserted and deleted tables contain more than one row) I simply log NULL as the old PK value ;-)

Here's the trigger I use :


CREATE TRIGGER CUSTOMERUPDATETRIGGER ON [Test$Customer]
FOR UPDATE AS
DECLARE @Datetime datetime
SELECT @Datetime = GETDATE()
DECLARE @NumChanged int
SELECT @NumChanged = count(*) FROM inserted
IF (@NumChanged = 1)
BEGIN
  DECLARE @NewID int
  DECLARE @OldID int
  SELECT TOP 1 @NewID = [Number] from inserted
  SELECT TOP 1 @OldID = [Number] from deleted
  INSERT INTO
    CustomerLog (CL_Datetime, CL_Number, CL_Operation, CL_OldNumber)
    SELECT @Datetime, [Number], 2, @OldID FROM inserted
END ELSE
BEGIN
  INSERT INTO
    CustomerLog (CL_Datetime, CL_Number, CL_Operation, CL_OldNumber)
    SELECT @Datetime, [Nummer], 2, NULL FROM inserted
END

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-03 : 12:13:54

MrBean, I'm glad you found a solution that works. My concern regarding PK's being updated comes from issues that arise, such as the one you are dealing with, where you want to store corrollary information about the row. This actually applies to any Foreign Key type of relationship, where an update to the PK of the parent table requires a cascade through all child tables.

In SQL 2000, you can create CASCADING Updates a lot easier than you used to. But then the question is, SHOULD YOU? And I'm sure that could start quite a debate. Maybe this is just my personal bias, but I think of the PK being the identifying characteristic of the row. If that identifying characteristic can change, then what was so important about it in the first place?

As for using Identity columns as a Primary Key... well, that's a debate best left dormant for now. Right Rob?

-------------------
It's a SQL thing...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources