Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cdbillups
Starting Member
2 Posts |
 Posted - 2008-02-07 : 12:05:58
|
| I'm new to SQL and am having some trouble figuring out Triggers. We use Microsoft Dynamics SL (formerly Solomon) for our accounting. We have a table that only has data in it while we're processing checks called PRCHECKTRAN. We'd like to build a report that uses the information in this table but it's only available until we "keep" the checks that we're printing. This creates an added step because we have to stop our Payroll process to go out and print the reports. It's been suggested to use a trigger to make a copy of the records in the PRCHECKTRAN table so we've got them after the checks are "kept." We're wanting something that's done behind the scenes so the user can continue to do things the way they always have while giving us access to the data past the payroll period.Basically as soon as we calculate checks, PRCHECKTRAN is populated. Once the checks are accepted as good, PRCHECKTRAN is cleaned out. I think I need an update or insert trigger that copies all records over to another table that we'll create called something like PRCHECKTRAN_HOLD. I'm just not familiar enough with SQL to write it. Any direction you can provide would be greatly appreciated. Thanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-07 : 12:10:48
|
| can you provide some info on structure of table, columns with some sample data? |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-02-07 : 12:30:28
|
| Just as a reality check - you may have already done this but:I would contact Solomon ( or MS SL ) and describe to them the report that you want. If they don't have one already, they may have a better alternative. They usually don't like customers messing with their database and you may void some support agreement by doing it.Be One with the OptimizerTG |
|
|
|
cdbillups
Starting Member
2 Posts |
Posted - 2008-02-07 : 13:01:05
|
| PRCheckTran - PR Check Detail This table contains the records that will be printed on check in Checks (02.630.00). The records are created by Calculation Process (02.500.00) and Review-Edit Check (02.030.00). Column Name Type DescriptionCheckPage Integer(2) Page number within check on which the detail line is to be printed.ChkNbr Character(10) Stub check numberChkSeq Character(2) Check sequence number used to determine which check the detail lines are for.ColiCurrAmt FLOAT (8) Earnings/Benefit current amountColi CurrUnits FLOAT (8) Earnings/Benefit current unitsColi Descr CHARACTER (30) Earnings/Benefit descriptionCol11d CHARACTER (10) Earnings/Benefit unique identification codeCol1NetPay INTEGER (2) Whether or not the amount does contribute to net payCol1Type CHARACTER (1) E - Earnins, B - Benefit, D - Direct Deposit DistributionCol1YTDAmt FLOAT (8) Earnings/Benefit YTD amountCol2CurrAmt FLOAT (8) The deduction current amountCol2Descr CHARACTER (30) The deduction descriptionCol21d CHARACTER (10) The deduction unique identification codeCol2YTDAmt FLOAT (8) The deduction YTD amountCpnylD CHARACTER (10) Company IDCrtd_DateTime DATE (4) The date when the record was createdCrtd_Prog CHARACTER (8) The program that created the recordCrtd_User CHARACTER (10) The user who created the recordEmpld CHARACTER (10) Employee unique identification code to whom the check will be issued.LinelD INTEGER (4) The field is reservedLineNbr INTEGER (2) In use by primary keyLupd_DateTime DATE (4) The date and time when the record was last updatedLupd_Prog CHARACTER (8) The program that last updated the recordLupd_User CHARACTER (10) The user who last updated the recordNoteld CHARACTER (10) Note ID if applicable This is the table layout / structure. There is no information in the table at this time since we don't calculate checks until tomorrow. Is there a way on update to select * from PRCHECKTRAN and write it to PRCHECKTRAN_BACKUP?Solomon suggested that I put a trigger on the database and that if I needed help with that they'd forward me on to their Premium Support. |
|
|
|
|
|
|
Trigger that runs on update