March 19, 2008 at 10:29 am
antonio.collins (3/19/2008)
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.
😎
This one reminds of s doozie we had. Some genius high up in the Adminisphere thought that some reports were really important and needed to "ring the bell". That's right - literally. They demanded that facility install a flashing red light and a alarm buzzer of sorts, wire it into the network (actually wire it into the serial port of the Intel NetPorts the printers were attached to), and we were to programmatically send some custom code/"print alert" to that over the wire, as the report gets printed.
Of course, they installed these printers everywhere (I don't even want to know how much they paid for that stuff), put us to work with a core of a product they had purchased off the NET...much blood and tears later, it went into play.
The first time the report printed in the clinical areas - the nurses thought they were either having a "code" of some kind, or some globalized crisis (remember - alarms buzzing and red lights flashing, everywhere, all at once...:)). One of the patients had a panic attack and heart palpitations. That was the last time that was ever used.....:cool:
Oh what kind of report was that you said? The departmental cost report, of course....
----------------------------------------------------------------------------------
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 1:33 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.
Antonio,
Hopefully by the moment I will have explained this to you I will understand it better myself 😉 or see a better way of implementation.
That’s what is behind the requirement, on the high level. There is a SQL Server 2000 database with financial information. End users [accountants] update it live via a front end application Great Plains [Microsoft product]. There are no means of filtering, validating, or restricting input from user inside Great Plains, so tons of garbage get in. We set up a stand-alone reporting SQL Server 2005 database. Data to be reported is replicated from the live database to the reporting database using off-shelf replication software SCRIBE. Now the most important thing. Accountants [end-users] violate SOP by retroactively updating old records in live database. They change this or that in the archive records that are not supposed to be changed, because they have been already reported. This naturally throws new [and re-run archived] reports off.
Management wants to capture all cases of SOP violations by flagging those changed records in our reporting database. So during incremental updates that SCRIBE will be performing in the Reporting database we want to flag all the records that are different from their previous version [replicated say 6 hrs ago] in >=1 field. It means that we want to update the *UpdateDate* field only for those records that were changed from the last replication. SOP regulates that once a record is put in it cannot be changed.
Writing a row–level trigger was the first thing that came to my mind.
Thanks for helping,
Sergei
March 20, 2008 at 8:50 am
well sergei, it wouldn't be the first time i've seen triggers used to try and clean up data that the front-end should handle/restrict.
i am surprised that your Great Plains install is so lax. i've installed GP 7.5-9.0 in a couple of companies and found it to be highly configurable, especially since it allows you to write extensions. and accountants should know that once a period is closed, the only way any change can be done is through a ledger entry.
those dang users are always making life miserable for us!:D
December 9, 2009 at 3:11 am
Hi Folks
I know this is a fairly old thread but here's an example which may be of use to others searching for an answer to this type of problem. In the example I have a date field Updated for the modified date and a varchar field UpdatedBy for the user's name. The use of the Columns_Updated() function ensures the trigger only updates the modified date and user when a change has occurred and the Case statements ensure we use the values provided if any. The Update() function is useful for testing if a specific columns has been changed rather than comparing the 'deleted' to 'inserted' column. It might not be the fanciest solution but it is efficient, it reads well and easily maintained.
Hope this helps someone.
Regards
Paul
SET NOCOUNT ON
IF (COLUMNS_UPDATED() > 0) BEGIN
UPDATE dbo.myTable
SET
[Updated] = CASE
WHEN Update([Updated]) THEN inserted.[Updated]
ELSE GetDate()
END,
[UpdatedBy] = CASE
WHEN Update([UpdatedBy]) THEN inserted.[UpdatedBy]
ELSE user_name()
END
FROM dbo.myTable
INNER JOIN inserted
ON dbo.myTable.ID = inserted.ID
END
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply