Friday, March 30, 2012

How do I make a field automatically get its values from anothe

David,
Thanks for your response. We're using an Access Form as a front-end to enter
data into this SQL Server table. So I'm not really using a storedproc to
enter data. Would using a trigger the only way to handle this then?
Thanks,
Sam
"David Portas" wrote:

> Sam wrote:
> I'm assuming you'll use stored procs for your inserts of course. So use
> an optional parameter and assign the default in the proc:
> CREATE TABLE dbo.PaymentSchedule (PaymentDate SMALLDATETIME NOT NULL,
> UpdatedPaymentDate SMALLDATETIME NOT NULL /* ... key? */);
> GO
> CREATE PROCEDURE dbo.usp_PaymentScheduleInsert
> (
> @.PaymentDate SMALLDATETIME,
> @.UpdatedPaymentDate SMALLDATETIME = NULL
> )
> AS
> INSERT INTO dbo.PaymentSchedule (PaymentDate, UpdatedPaymentDate)
> VALUES (@.PaymentDate, COALESCE(@.UpdatedPaymentDate,@.PaymentDat
e));
> GO
> EXEC dbo.usp_PaymentScheduleInsert
> @.PaymentDate = '2006-04-30T00:00:00.000' ;
>
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>Sam (Sam@.discussions.microsoft.com) writes:
> Thanks for your response. We're using an Access Form as a front-end to
> enter data into this SQL Server table. So I'm not really using a
> storedproc to enter data. Would using a trigger the only way to handle
> this then?
Yes, but I guess David's hint is that you should start using stored
procedures.
The trigger would look like:
CREATE TRIGGER sams_trigger ON tbl FOR INSERT AS
UPDATE tbl
SET updatedpaymentdate = i.paymentdate
FROM tbl
JOIN inserted i ON tbl.pkcol = i.pkcol
However, judging from the narrative, it seems to me that it would be
better to leave the column NULL. I'm assuming then that when the data is
entered, there has been no update to the payment date yet.
Then again, it was not clear to me whether this column is intended to
catch the date the client actually paid, or if this is a date agreeed-on
beforehand as the new date for the payment.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

No comments:

Post a Comment