October 8, 2008 at 10:29 am
Tableone has 5 columns (id, Lname, Fname, statues, manger)
Tabletwo has 7 columns (id, Lname, Fname, statues, manager, report, modifieydate)
Am using SSIS slowly changing dimension to update the changing attributes from tableone to tabletwo. And what I want to do is update the modifieydate for modification of any field except the report,. The report column is a calculated using (status and manger)
So I created a trigger
create TRIGGER [GOODTRI] ON [dbo].[tabletwo]
AFTER UPDATE AS
BEGIN
SET NOCOUNT ON;
IF NOT UPDATE (report)
BEGIN
UPDATE [dbo].[tabletwo]
SET [dbo].[tabletwo].modifieydate = GETDATE()
WHERE [dbo].[tabletwo].id IN (SELECT inserted.id FROM inserted )
END
END;
And it doesn’t update modifieydate even when there is a change in any of the field ( Eg, lastname) the other thing is that when I remove not
IF NOT UPDATE (report)-Line the modifieydate date in update to refelect all changes including in report column ..
create TRIGGER [GOODTRI] ON [dbo].[tabletwo]
AFTER UPDATE AS
BEGIN
SET NOCOUNT ON;
UPDATE [dbo].[tabletwo]
SET [dbo].[tabletwo].modifieydate = GETDATE()
WHERE [dbo].[tabletwo].id IN (SELECT inserted.id FROM inserted )
END;
Need to do this .. any help
October 8, 2008 at 11:55 am
The SCD wizard generates an OLEDB Command component that essentially updates every field regardless of it having changed. So, if any field on a record is changed, it updates all of the updatable fields to the new value.
The statement looks like:
UPDATE MyTable SET Field1=?, Field2=? WHERE ID=?
If the value of Field1 is the same, it is still updated, it just gets updated to the same value. Because of this, it meets the UPDATED() criteria.
You would have to check the INSERTED and DELETED values to determine if the field data is really different.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply