Prev: scope_identiry
Next: Selecting largest for each group
From: DavidC on 2 Jun 2010 13:10
Is it possible to run an UPDATE statement in a user defined function? I am
creating a UDF that determines the amount of a payroll deduction. Some
deductions have a balance (total) to deduct over time. If a deduction is a
balance deduction then I want to be able to reduce the balance by the amount
of the deduction. For example, a deduction has an amount of $10 and a
balance of $100. When I take this deduction I need the balance to be reduced
to $90. Below is the table schema that I am using. Thanks for any help on
this or if someone can point out a different way, I would appreciate it.
Thanks.

CREATE TABLE [dbo].[WorkerDeductions](
[WorkerDeductionID] [int] IDENTITY(1,1) NOT NULL,
[PeopleLinkID] [int] NOT NULL,
[DedCode] [int] NOT NULL,
[VendorID] [int] NOT NULL,
[DedAmt] [smallmoney] NULL,
[DedBalance] [smallmoney] NULL,
[DedPercent] [smallmoney] NULL,
[ReimbRate] [smallmoney] NULL,
[DedNote] [nvarchar](50) NULL,
[DedStart] [date] NULL,
[DedEnd] [date] NULL
--
David
From: Rich on 2 Jun 2010 13:29
If you are updating a row when deduction is made -- I would use a trigger to
update your table based on the action taken (the deduction). If you are
adding a row to your table to show the history of the account, then you could
use a udf to calculate the new balance. I think you could still also use a
trigger to update the new record.

Rich

"DavidC" wrote:

> Is it possible to run an UPDATE statement in a user defined function? I am
> creating a UDF that determines the amount of a payroll deduction. Some
> deductions have a balance (total) to deduct over time. If a deduction is a
> balance deduction then I want to be able to reduce the balance by the amount
> of the deduction. For example, a deduction has an amount of $10 and a
> balance of $100. When I take this deduction I need the balance to be reduced
> to $90. Below is the table schema that I am using. Thanks for any help on
> this or if someone can point out a different way, I would appreciate it.
> Thanks.
>
> CREATE TABLE [dbo].[WorkerDeductions](
> [WorkerDeductionID] [int] IDENTITY(1,1) NOT NULL,
> [PeopleLinkID] [int] NOT NULL,
> [DedCode] [int] NOT NULL,
> [VendorID] [int] NOT NULL,
> [DedAmt] [smallmoney] NULL,
> [DedBalance] [smallmoney] NULL,
> [DedPercent] [smallmoney] NULL,
> [ReimbRate] [smallmoney] NULL,
> [DedNote] [nvarchar](50) NULL,
> [DedStart] [date] NULL,
> [DedEnd] [date] NULL
> --
> David
From: DavidC on 3 Jun 2010 19:11

"Rich" wrote:

> If you are updating a row when deduction is made -- I would use a trigger to
> update your table based on the action taken (the deduction). If you are
> adding a row to your table to show the history of the account, then you could
> use a udf to calculate the new balance. I think you could still also use a
> trigger to update the new record.
>
> Rich
>
> "DavidC" wrote:
>
> > Is it possible to run an UPDATE statement in a user defined function? I am
> > creating a UDF that determines the amount of a payroll deduction. Some
> > deductions have a balance (total) to deduct over time. If a deduction is a
> > balance deduction then I want to be able to reduce the balance by the amount
> > of the deduction. For example, a deduction has an amount of $10 and a
> > balance of $100. When I take this deduction I need the balance to be reduced
> > to $90. Below is the table schema that I am using. Thanks for any help on
> > this or if someone can point out a different way, I would appreciate it.
> > Thanks.
> >
> > CREATE TABLE [dbo].[WorkerDeductions](
> > [WorkerDeductionID] [int] IDENTITY(1,1) NOT NULL,
> > [PeopleLinkID] [int] NOT NULL,
> > [DedCode] [int] NOT NULL,
> > [VendorID] [int] NOT NULL,
> > [DedAmt] [smallmoney] NULL,
> > [DedBalance] [smallmoney] NULL,
> > [DedPercent] [smallmoney] NULL,
> > [ReimbRate] [smallmoney] NULL,
> > [DedNote] [nvarchar](50) NULL,
> > [DedStart] [date] NULL,
> > [DedEnd] [date] NULL
> > --
> > David

The WorkerDeductions table is only updated after I have created a record in
a different record that is linked to the paycheck. The trigger idea sounds
good but not sure how to do that as only those deductions with a balance need
to be updated.

--
David


 | 
Pages: 1
Prev: scope_identiry
Next: Selecting largest for each group