I have a table like this where I keep a list of appointments which increment based on the combination of A and B:
ID A B Appointment Count
-----------------------------
1 abc 0 2010-10-20 1
2 abc 0 2010-10-25 2
3 abc 0 2010-10-30 3
3 abc 1 2010-10-30 1
4 xyz 1 2010-08-18 1
5 xyz 1 2010-08-19 2
6 xyz 1 2010-08-20 3
And a function like this:
CREATE FUNCTION dbo.GenerateCount
(
@id int,
@A int,
@B int,
@appt_date date
)
RETURNS Int
AS
BEGIN
RETURN
(
SELECT COUNT(*)
FROM dbo.test_seq
WHERE patient_id = @A
AND B = @B
AND id <= @id
AND appt_date <= @appt_date
)
END
With data inserted like this:
CREATE TABLE [dbo].[test_seq](
[id] [int] IDENTITY(1,1) NOT NULL,
[A] [int] NOT NULL,
[B] [int] NOT NULL,
[appt_date] [date] NOT NULL,
[count] AS dbo.GenerateCount(id, A, B, appt_date)
)
When I insert a new entry in the table, it increments the count as expected. However if I insert a new entry with a date in the middle, say if I want to add:
ID A B Appointment Count
-----------------------------
1 abc 0 2010-10-21
it has the correct count assigned, but the other rows don't get updated. How can I trigger a table update for all the other records after that date so they are corrected with the relevant count values?
I tried creating a trigger on insert/update/delete, but that only applies to the row being inserted and not the whole table.
If I get this correctly, the simple answer is: Don't!
A SQL-Server-table is not Excel...
You must decide
Create a VIEW upon your table (according to the approach you find in your last question).
This will compute the correct numbers whenever you call that.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments