November 24, 2009 at 8:02 am
Hi all - before I implement this trigger I'd like some review to ensure this is doing what I want it to do.
CREATE TRIGGER [Trigger]
ON [Table]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
WITH cte AS
(
SELECT DISTINCT
[Columns]
FROM INSERTED i
JOIN DELETED d ON [Identity Column]
WHERE [Inserted Column] != [Deleted Column]
)
UPDATE c1
SET [Field To Be Updated] = [Field That Was Updated]
FROM [Table To Be Updated] c1
JOIN cte c2 ON [Columns]
INSERT INTO [History Table] ([Fields])
SELECT [Fields]
FROM cte c1
JOIN [Table to Be Updated] c2 ON [Columns]
END
GO
What's happening is that we're moving functionality from one database to a new one. But, while the move is taking place, I want to make sure that anything happening on the old database is reflected on the new one. I've updated most of my code to do that already, but this trigger is in place just in case I've missed anything.
Since the update could be happening upon multiple records, what I'm doing is storing the the records to be updated in a CTE, then updating all the records together. Then, when that is done, I'm storing the records that were updated in a table just to see what has happened after the fact.
The columns being joined upon on the real tables are indexed, so everything should go by smoothly, right?
November 24, 2009 at 8:18 am
I assume you're working on the real syntax here. A couple things
1. The INSERT should fail as I believe the CTE will be out of scope at the end of the UPDATE. It's not something that exists for the batch, it exists for the statement.
2. the WHERE clause in your CTE needs to compare inserted to deleted, not updated. If this is psuedocode, that's fine, but it is a little hard to determine if this will work or if you misunderstand how this works. The thing I'm wondering is if row 1 updates columnA and row 2 updates columnB, what goes in your UPDATE SET list? It isn't as simple as ou might make it. I assume you might capture all columns, but how do you know what changed? Or do you care?
I would think that you just would insert the changes into another table and not worry about the update. If the update fails somehow, I'm not sure what your trigger update would accomplish?
November 24, 2009 at 8:24 am
Ah, definately true regarding the CTE not working for the INSERT ... hadn't considered that. That's annoying, because I'm told you're not supposed to use temp tables in triggers. Well, I suppose I don't actually need to update the table in the other database - really all I want is to have the records that should be updated, I can run the actual update myself later on if necessary.
As for the behavior of the CTE, what I'm trying to do is to capture any records in which any of a set of columns were changed. In this particular case I'm only looking at two columns - if, for a given row, either one of those columns was changed, IE the value in the INSERTED differs from the value in the DELETED for those columns, then I want to capture that row. So if rowA updates column 1 and rowB updates column 2, and both column 1 and column 2 are criteria I want to capture, then the CTE will contain two rows.
November 24, 2009 at 8:35 am
Ok, but if you update two columns, you CTE needs to have
where i.colA != d.colA
OR i.colB != d.colB
And so on for other columns. You have to think about listing each column. Also, your SELECT won't necessarily know which column then corresponds to which row. You have all columns in all rows, not different ones. So that even if ColA doesn't change in row 2, you'll have colA in there. So you need to think about how to handle this.
November 24, 2009 at 8:36 am
*nods*, that's the way I've got it set up, yeah. Since I'm going to just write this stuff to a history table now, and remove the direct update against the real table, I can just look at the data after wards and then determine what needs to be updated.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply