March 18, 2008 at 3:34 pm
Hi,
I have a tbl:
USE [Sergei_SandBox]
GO
/****** Object: Table [dbo].[test_2] Script Date: 03/18/2008 17:20:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[test_2](
[RowID] [int] NULL,
[CustID] [smallint] NULL,
[CustName] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AcctMngr] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UpdateDate] [datetime] NULL
) ON [PRIMARY]
--…populated with this data:
nsert test_2 values (1, '01', 'Malivaki', 'Bill Z', Null)
insert test_2 values (2, '02', 'Bender', 'Partizan', Null)
insert test_2 values (3, '03', 'Febder', 'Luongo', Null)
insert test_2 values (4, '04', 'Kuriaga', 'Lebeder', Null)
insert test_2 values (5, '05', 'Navuxor', 'Vuorkaj', Null)
insert test_2 values (6, '06', 'Sergei', 'Barry Trotz', Null)
Update test_2 set UpdateDate = '01/01/08'
select * from test_1
The requirement I’m facing is such: Whenever a record [any field] gets UPDATEd, update field ‘UpdateDate’ as
Set UpdateDate = getdate()
Say [AcctMngr] gets changed from 'Bill Z' to 'Bill Y',
It should trigger ***Set UpdateDate = getdate()*** for THIS ONLY ROW.
I’m considering Update Trigger. That’s what I have so far figured for only one field [AcctMngr]:
CREATE TRIGGER tg_UpdateDate
ON dbo.test_2
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE dbo.test_1 SET UpdateDate= GETDATE()
WHERE RowID IN
(
Select i.RowID FROM Inserted i INNER JOIN Deleted d
ON i.RowID = d.RowID
WHERE i.AcctMngr != d.AcctMngr
)
END
GO
RowID is not being updated according ot business logic.
It should work [still testing] but the problem is that I have real tables with dozens of fields. Is ther another way of doing the Update without OR-ing all possible columns in WHERE clause of the trigger?
Thanks for your time,
Sergei
March 18, 2008 at 3:43 pm
Don't waste your time on that... answer a quick question instead...
Triggers fire on any update... If I update a name from 'Jeff' to 'Jeff' (not a type-o, they are the same), do you want the trigger to update the modified date, or not?
Answer that, and then one of the two ways to write your trigger will become apparent to me...
--Jeff Moden
March 18, 2008 at 4:04 pm
Hi, Jeff...it's good to hear from you again ;=)
I'm beng told that Update will constitute actual change in at least one [ or more] of the fields within a row. So IF ONLY one field on a row gets updated from *Jeff* to *Jeff* I don't have to update UpdateDate.
March 18, 2008 at 6:45 pm
Hi, Jeff...it's good to hear from you again ;=)
Good to "see" you again, too. You should come around more often... 😉
Ok... this should do it... this will work in either SQL Server 2000 or 2005 (maybe even in SQL Server 7). Important stuff is in the comments.CREATE TRIGGER tg_UpdateDate
ON dbo.Test_2
AFTER UPDATE
AS
--===== Suppress the auto-display of row-counts to prevent accidental
-- indication of errors and unwanted secondary result sets
SET NOCOUNT ON
--===== Update the "modified date" column but only if an actual change
-- of data has occurred somewhere in the row.
-- Because BINARY_CHECKSUM was used, it will detect changes in
-- case and accent markings, as well.
UPDATE dbo.Test_2
SET UpdateDate = GETDATE()
FROM (SELECT RowID, BINARY_CHECKSUM(*) AS MyCheck FROM Deleted) d
INNER JOIN
(SELECT RowID, BINARY_CHECKSUM(*) AS MyCheck FROM Inserted) i
ON d.RowID = i.RowID
AND d.MyCheck <> i.MyCheck
INNER JOIN dbo.Test_2 u
ON d.RowID = u.RowID
Had to use Derived tables (or, could use CTE's) because the checksums won't take something like (d.*) as an argument.
--Jeff Moden
March 18, 2008 at 6:46 pm
i think you're making a mountain out of a molehill.
if you open a word, excel, or even a text file and then click file>save, the file will get a new modified date. period. it doesn't matter if you made changes or not.
simplify your life and set table.UpdateDate to getdate() for all items that are in the inserted table.
March 18, 2008 at 7:08 pm
antonio.collins (3/18/2008)
i think you're making a mountain out of a molehill.if you open a word, excel, or even a text file and then click file>save, the file will get a new modified date. period. it doesn't matter if you made changes or not.
simplify your life and set table.UpdateDate to getdate() for all items that are in the inserted table.
Heh... Ya just gotta learn how to read these posts, antonio... 😛
I'm beng told that Update will constitute actual change in at least one [ or more] of the fields within a row
See the operative words? It's not like Sergei really has a choice... besides, it's not difficult to do nor is it an unusual request for audits... especially SOX audits. 😉
--Jeff Moden
March 18, 2008 at 7:34 pm
Jeff,
thank you for explaining this. You are a big help as usual. I'll test and get back with results.
Sergei
March 18, 2008 at 7:37 pm
betcha $20 it's a case of "that is what was said, but this is what was meant". sergei is already spending more time on this than should be necessary and with some pushback his bosses should know that time is money. and from a pure data standpoint, the record has changed (it was deleted and recreated) so the fresh update date is not misleading anyone. personally, if a GUI is being used, it should be smart enough not to send superfluous update statements (most DataGrids and PowerBuilder's datawindow certainly won't).
and i guarantee you the person that set the "requirement" can't read code and wouldn't know the difference. 😛
my slogan is "make my life easier, or i'll make your's more difficult."
March 18, 2008 at 8:22 pm
and i guarantee you the person that set the "requirement" can't read code and wouldn't know the difference
Heh... you must be having a bad day... No? I guarantee you will have if the person who set the requirement ever finds out... and it always comes out eventually... 😉 You would take such a chance on something so simple to write code for?
And, it takes longer to push-back on the bosses than it does to do it.
--Jeff Moden
March 18, 2008 at 8:51 pm
Sergei Zarembo (3/18/2008)
Jeff,thank you for explaining this. You are a big help as usual. I'll test and get back with results.
Sergei
Thanks, Sergei.
--Jeff Moden
March 18, 2008 at 8:57 pm
i challenge pointless requirements. i want to know why a requirement exists because i've seen far too many 'requirements' turn out to be unused and unneeded.
what happens if they want to track changes on 6 out of 10 columns? and then they change their minds and make it 7 out of 10? and you have 50 tables to track and they want to pick and choose which columns for all tables? and all table triggers are maintained/generated by powerdesigner? not such a simple task now is it?
simplifying the requirement to cause any update to refresh "updateDate" (notice the name of the column) makes life simple and lets developers get on with more important matters and i'll venture doesn't affect the business' data processing one iota.
March 18, 2008 at 9:18 pm
i challenge pointless requirements. i want to know why a requirement exists because i've seen far too many 'requirements' turn out to be unused and unneeded.
I absolutely agree with that and the DevMgr should definitely go to bat over something really stupid. But this isn't a stupid requirement... and when they say "6 out of 10" columns, it's still not necessarily stupid. There might actually be a good reason for it and it's still not AI or rocket science.
And, it's not up to a Developer to simply skip or intentionally "deceive" a requirement. They need to tell the Dev Manager if they get something they think is stupid. The DevMgr can tell the users/customers that unless they can come up with a real strong case for a requirement that the Developer disagrees with, it won't go in... but if one of the Developer's makes that decision, I hope (s)he likes standing in line...
Light Yellow lettering on a White background in a GUI? Screen saver that kicks in every 5 seconds? Now, those just might be stupid... but it's still not up to the Developer to skip or change the requirement. 🙂
--Jeff Moden
March 19, 2008 at 9:36 am
we all need to do what we're told, but we shouldn't immediately roll over.
sorry, but my cynicism on pointless requirements has been well honed over the past two decades. fortunately i can push back directly to the requester -- but when i couldn't i found far too many project managers who would never challenge anything, big or small.
every requirement should go through a simple test with 3 questions:
1) what's the purpose and benefit? whoever's adding the req needs to answer this. if both the purpose and benefit can't be clearly defined, the requirement should be shelved.
2) what's the cost? this estimate has to come from the implementers and users/consumers of the requirement. management and the requester can't answer this.
3) is the benefit worth the cost? this is management's decision and there needs to be accountability to the decision.
but this simple test is often not applied. requirements wind up being dictated with little open discussion between parties. i have no problem doing justifiable work even if it's difficult or even if there's a simpler alternative that could satisfy 9 out 10 needs. but that tenth need must be justified.
here's a perfect example. i worked on a legacy replacement system that received some of its data from a mainframe feed (the feed was scheduled to be replaced later). the feed included data that should not be processed or even seen by users. of course, no one told us about this data (missing requirement), so when the system goes into alpha all heck breaks loose. i remember the VP of IS telling us "management has lost confidence in your ability to execute a project of this magnitude."
the logical fix was straightforward -- just filter that data from the feed. but a new "requirement" was established that we should instead filter the data from all access routines. someone decided that the data 'may be' needed in the future so off we go adding the filter to all reports and all queries. nevermind that it bloated the database by about 5%. nevermind the extra development and testing involved to implement the filter. it was a "requirement". and by the way, when the mainframe feed was finally replaced the next year, the new feed didn't include that data.
then there was the requirement to sort incoming data and only process one timezone at a time -- even though all times were already adjusted to EST. another one required that if the app was creating a new item, it had to insert it with dummy values and then update it with the real values. this apparently due to some audit table needing a creation event and an update event -- i can't remember since there were never any audit reports created.
another one that forced the user to enter their password after 5 minutes of inactivity (yes, five!). and the classic GUI requirement that checkboxes for this field show a 'c' (for completed) rather than 'x' and that field show a 'r' (for rush) and the other field... you get the picture. all inspite of the checkbox having a label beside it that says 'completed', 'rush', etc.
my 'favorite' requirement was to send a report a particular printer after the first data load of a month. all other scheduled reports resided on disk and the user just viewed or printed what they wanted. but that was unacceptable to one production manager. ("you can't change our procedures. we were told that the new system would not impact us at all.")
now this printer was attached to a mainframe spooler that was not accessible to the unix/windows network so satisfying this requirement was a challenge. it meant we needed to write a new report with embedded printer control sequences (existing output formats where .xls, .pdf, and text). it meant exposing that printer to the network so the mainframe support and lan support groups got involved. since only certain people could access it the windows domain group needed to map domain users to mainframe users. this back-and-forth between departments went on for a few weeks until a big meeting was called with twenty folks from five or six departments. 30 minutes into the meeting, the production supervisor (who was just sitting in for the production manager who demanded the report) realized what report was being discussed. then she announced that they never used that report and had never been able to figure out how to make it stop printing automatically.
😎
March 19, 2008 at 9:52 am
antonio.collins (3/19/2008)
but this simple test is often not applied. requirements wind up being dictated with little open discussion between parties. i have no problem doing justifiable work even if it's difficult or even if there's a simpler alternative that could satisfy 9 out 10 needs. but that tenth need must be justified.
(I'm not going to quote the whole thing)
Let me start by saying - I push back when I think something is stupid, and I push back HARD. That being said - it's our responsibility to point out better methods, and the fallout of using "stupid methods"; it's however not our responsibility to go quite as far as REFUSING to do something when it's "not justified" in our minds. Some amount of our job is "not to question why, only to stand and die". We might be technical and all, but by far and large - we're grunts, and we need to follow orders....
In other words - fight the good fight, and when you lose the argument, document the conversation, and then - well, implement whatever was decided.
Heck - if I had refused to do every "stupid" thing requested of me, I wouldn't have nearly as much work today....:D
----------------------------------------------------------------------------------
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?
March 19, 2008 at 10:11 am
but when i couldn't i found far too many project managers who would never challenge anything, big or small.
Now, there's something I agree with... that's why the "shop" needs a really good dev manager that keeps the loonies off the developers.
i remember the VP of IS telling us "management has lost confidence in your ability to execute a project of this magnitude."
I feel for ya... That's why I got out of management... depending on what context that was in, it could certainly be the sign of a VP that has trully been promoted to his/her level of incompetence. Real tough to keep the dogs out when you work for one.
Thanks for the feedback, antonio...
--Jeff Moden
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply