April 2, 2008 at 11:19 am
ALZDBA (4/1/2008)
Indeed, changes to the underlying table's column order will make a bitmapped trigger buggy, and there is no way you can avoid it becomming buggy ...Best is to seal it with a KISS (keep it simple and stupid) :w00t:
Well - there IS a way...but it's usually worse than the cure. You'd have to go querying Information_schema.columns for the "sequence number" of the column, and use that information to populate your bitmask (so Sequence 2 turns into 2^ (2-1)=2, sequence 3 into 2^(3-1)=4, etc...).
Of course - that's an AWFULLY long way to go when you have the UPDATED(column) syntax available...:)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 2, 2008 at 5:34 pm
Many .NET apps will write values to several fields even if the values have not changed. Am I wrong about that?
No, you're absolutely right.
Programming skills of most of developers nowadays are awfull.
Since you are working inside a trigger, wouldn't it be better to compare the values between the inserted and deleted tables?
Since you're you are working on data entry point wouldn't it be better to update the table only with data which were actually changed?
_____________
Code for TallyGenerator
April 2, 2008 at 5:37 pm
mtassin, you forgot to take care about NULLs.
Also, your script gonna miss the case when user decides to add a space to a string.
_____________
Code for TallyGenerator
April 3, 2008 at 12:12 am
Matt Miller (4/2/2008)
Well - there IS a way...but it's usually worse than the cure. You'd have to go querying Information_schema.columns for the "sequence number" of the column, and use that information to populate your bitmask (so Sequence 2 turns into 2^ (2-1)=2, sequence 3 into 2^(3-1)=4, etc...).Of course - that's an AWFULLY long way to go when you have the UPDATED(column) syntax available...:)
Indeed.
As stated in BOL SQL2005 (could not find this in BOL SQL2000)
Caution:
In SQL Server 2005, the ORDINAL_POSITION column of the INFORMATION_SCHEMA.COLUMNS view is not compatible with the bit pattern of columns returned by COLUMNS_UPDATED. To obtain a bit pattern compatible with COLUMNS_UPDATED, reference the ColumnID property of the COLUMNPROPERTY system function when you query the INFORMATION_SCHEMA.COLUMNS view, as shown in the following example:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 3, 2008 at 3:16 am
Hai Alex,
I have a similar problem ,where i want to fire a trigger if a particular column in a table is updated.
Let us consider the following
I Have a table with name called 'CallDetail' with Columns city,time,country,callnumber
i want to fire a trigger only when callnumber column is updated ,so i wrote the trigger as below and i didn't got correct solution.
CREATE TRIGGER tr_CallDetail ON CallDetail
AFTER UPDATE (CallDetail)
as
Begin
select 'Trigger called as callnumber updated'
end
So,Kindly help me in this regard
Thanks
Rajesh
April 3, 2008 at 3:55 am
Your update trigger needs to be firred !
Then you can check the update content.
CREATE TRIGGER tr_CallDetail ON CallDetail
AFTER UPDATE
as
Begin
if UPDATE (callnumber)
begin
-- this means the callnumber column is used in the update.(not always updated)
select 'Trigger called as callnumber updated'
end
end
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 3, 2008 at 8:12 am
Sergiy (4/2/2008)
mtassin, you forgot to take care about NULLs.Also, your script gonna miss the case when user decides to add a space to a string.
I don't have the structure of the table... but it's easy enough to encapsulate the field comparisons with isnulls.
If they added a space to the string, they changed it. Since we don't know what the fields are, this is valid.
In either case IF UPDATE is worse than anything I proposed. I've seen multi-million dollar ERPs update one column and set the rest of the table's values to the same value as they already were. IF UPDATE would say that something had happened when nothing did.
Joining inserted to deleted will let you detect changes.
April 3, 2008 at 8:25 am
For a membership database, I was asked to automatically create a note with the "before" values when a member's record was changed. There were about 25 fields they wanted to track, and the full trigger is several pages long, but here is a brief example of how I handled text, dates, and numbers...
CREATE TRIGGER [dbo].[trgAuditLog_Person] ON [dbo].[Person]
FOR UPDATE
AS
DECLARE
@txt varchar(8000),
@stOld varchar(100),
@stNew varchar(100),
@nmOld int,
@nmNew int,
@dtOld smalldatetime,
@dtNew smalldatetime,
@rows int
SELECT
@txt = '',
@stOld = '',
@stNew = '',
@nmOld = 0,
@nmNew = 0,
@dtOld = '01/01/1900',
@dtNew = '01/01/1900',
@rows = @@ROWCOUNT
-- skip this for mass updates
IF @rows > 1
RETURN
-- NRDSOfficeID
SELECT @nmOld = 0, @nmNew = 0
SELECT
@nmOld = ISNULL(d.NRDSOfficeID, 0),
@nmNew = ISNULL(i.NRDSOfficeID, 0)
FROM
Deleted d
INNER JOIN inserted i
ON i.PersonID = d.PersonID
WHERE
ISNULL(d.NRDSOfficeID, 0) != ISNULL(i.NRDSOfficeID, 0)
IF @nmOld != @nmNew
SET @txt = @txt + 'NRDSOfficeID ' + CAST(@nmOld AS varchar) + '; '
-- MemberType
SELECT @stOld = '', @stNew = ''
SELECT
@stOld = ISNULL(d.MemberType, '{none}'),
@stNew = ISNULL(i.MemberType, '{none}')
FROM
Deleted d
INNER JOIN inserted i
ON i.PersonID = d.PersonID
WHERE
ISNULL(d.MemberType, '{none}') != ISNULL(i.MemberType, '{none}')
IF @stOld != @stNew
SET @txt = @txt + 'MemberType ' + @stOld + '; '
-- JoinedDate
SELECT @dtOld = '01/01/1900', @dtNew = '01/01/1900'
SELECT
@dtOld = ISNULL(d.JoinedDate, '01/01/1900'),
@dtNew = ISNULL(i.JoinedDate, '01/01/1900')
FROM
Deleted d
INNER JOIN inserted i
ON i.PersonID = d.PersonID
WHERE
ISNULL(d.JoinedDate, '01/01/1900') != ISNULL(i.JoinedDate, '01/01/1900')
IF @dtOld != @dtNew
SET @txt = @txt + 'JoinedDate ' + CONVERT(varchar, @dtOld ,1) + '; '
IF LEN(@txt) > 0
INSERT
MemberNotes
(
PersonID,
[Description],
CreatedDate,
EnteredBy,
LastUpdatedDate,
LastUpdatedBy
)
SELECT
i.PersonID,
'was: ' + @txt,
GETDATE(),
'auto-note',
GETDATE(),
'auto-note'
FROM
inserted i
LEFT JOIN Person p
ON p.PersonID = i.UpdatedBy
April 3, 2008 at 2:59 pm
I don't have the structure of the table... but it's easy enough to encapsulate the field comparisons with isnulls.
If you don't know something you must assume the worst.
And if it's so easy why you dod not do it?
If they added a space to the string, they changed it. Since we don't know what the fields are, this is valid.
No, it's not valid.
Your statement will take strings 'The Value' and 'The Value ' as equal and will not run the code in the trigger.
Same thing about changing case for some letters. It will be ignored by the trigger as well, unless you have case sensitive collation.
_____________
Code for TallyGenerator
April 3, 2008 at 3:07 pm
Sergiy (4/3/2008)
I don't have the structure of the table... but it's easy enough to encapsulate the field comparisons with isnulls.
If you don't know something you must assume the worst.
And if it's so easy why you dod not do it?
Laziness... of course 😉
If they added a space to the string, they changed it. Since we don't know what the fields are, this is valid.[/quote No, it's not valid.
Your statement will take strings 'The Value' and 'The Value ' as equal and will not run the code in the trigger.
Same thing about changing case for some letters. It will be ignored by the trigger as well, unless you have case sensitive collation.
Since we're supposed to assume the worst per above, that could be the difference between one product code and another. We're also talking about dealing with something that the user did to update the data. I don't know your situation, but I've seen users type in the space intentionally trying to get the trigger/program code to fire again.
Say for instance, a third party process runs when the record is updated if a certain field changes (such as automatically generating a printed or pdf version of a sales order/invoice). They don't actually want to change the contents at all, just get the process to run again. So they add a space onto the end of a field that doesn't matter if there is another space on it.
Bingo the trigger processes, and perhaps re-adds the Sales Order to a process queue to be batched out and faxed. Yah it's crude, but sometimes it's easier than trying to get an ERP vendor to make a customization to have a "send this again" button added to an order faxing system.
In a perfect world, IF UPDATE is the way to go. When you can't go back and get an application fixed to only update the field(s) that have changed, and have some general process/object that updates all of the fields when a record is updated, you can't use it. You have to check for a record change, not if the field was updated.
Yes, above I didn't bother with isnulling the columns because I have no idea if NULLS are allowed within the columns. You can argue this either way and I'm not going to worry about it either way. I know what ISNULL is, I've used it for over 10 years. Since this was at best a theoretical exercise without table structures and the like, I'm not sweating the inclusion or exclusion of it in the query I gave as a theorectical solution to a theoretical problem.
April 3, 2008 at 4:39 pm
mtassin, I'm not trying to say you don't know how to use ISNULL. I don't know you at all, so I assume you're the best in SQL. 🙂
I'm just trying to say it's not so simple and clear as you say and in case of a big table it makes the code look horrible.
As for me the overhead of such code in a trigger is much bigger than processing "non changing updates". Not to mention about potential data losses due to matching rules related to the collation used for the table/column.
And another thing (probably it's mainly not for mtassin but for OP).
Triggers are integral parts of tables. They are not part of an application code, they are part of an object described in the table, like constaints. Triggers are executed not only when you run your application but every time anybody doing anything to the object, even from EM (which is an application as well :)).
So, they must have inside only the code related to THE NATURE OF OBJECT stored in the table, not to some process you need to execute against newly inserted data.
Otherwise you'll end up in the company with another guy here asking how to disable-enable triggers on fly.
_____________
Code for TallyGenerator
April 4, 2008 at 7:21 am
In Sybase T-SQL (where I spend most of my time) UPDATED simply means that the column has been specified in an UPDATE command. It does not mean that the contents of the column have changed.
So far as I can see from one simple test, the same applies in MSSQL.
This makes UPDATED not particularly useful if you are invoking sprocs that specify the new contents of every column whenever an UPDATE is required.
If the trigger has to identify which columns have actually changed, you'll need to compare the columns in INSERTED and DELETED, making appropriate provision for NULLs.
April 4, 2008 at 7:29 am
Stephen Grimshaw (4/4/2008)
In Sybase T-SQL (where I spend most of my time) UPDATED simply means that the column has been specified in an UPDATE command. It does not mean that the contents of the column have changed.So far as I can see from one simple test, the same applies in MSSQL.
This makes UPDATED not particularly useful if you are invoking sprocs that specify the new contents of every column whenever an UPDATE is required.
If the trigger has to identify which columns have actually changed, you'll need to compare the columns in INSERTED and DELETED, making appropriate provision for NULLs.
It essentially allows for a rough cut at figuring out which columns MIGHT have been updated. It is also an "operation-level" answer instead of a row-level answer. In short - it's a much lower level of effort than checking at a row level whether the value in inserted is different than that in deleted.
So a. you can focus on just the 4 columns (out of 50 in the table) that were even included in the UPDATE, and you don't have to run a join between 2 100,000 row tables to figure that out.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 5, 2008 at 3:05 pm
Stephen Grimshaw (4/4/2008)
In Sybase T-SQL (where I spend most of my time) UPDATED simply means that the column has been specified in an UPDATE command. It does not mean that the contents of the column have changed.So far as I can see from one simple test, the same applies in MSSQL.
No, you're wrong.
UPDATED means that the column have been changed.
It means that the page with old value was deleted from the table and copied to LOG file, the new page with the value set by UPDATE statement is created and included into the table.
So, the data set is actually changed. You may get the proof from analyzing the LOG file.
But if the new value is the same as old one FROM CLIENT'S POINT OF VIEW - it's not for DMS to say.
It's part of the business rules telling what is the same and what is different. For some systems "same time" means "within the same second". Does it mean that values '2008-04-05 15:15:15.150' and '2008-04-05 15:15:15.450' are the same?
_____________
Code for TallyGenerator
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply