My details view shows current job position by linking a person to a position in a bridging table called tblPositionFill.
The fields in that table are:
PositionFillID (PK)
PositionID (FK)
PersID (FK)
FromDate (DateTime)
ToDate (DateTime)

What I would like to do is either:
1. Prevent user from inserting a new row unless he closes out the current row by entering a ToDate for the position. (Prompt user accordingly)
-or-
2. Automatically upate the current position ToDate upon entry of a new position assignment.
-or-
3. Upon terminating current assignment, switch the detailsview to insert and prompt the user to create the new assignment.

(or perhaps a combo of the three)

In the back of my head, I am thinking "Trigger" in SQL Server. I have never created nor used one. I'm gonna play with that for now...hopefully my favorite .Net guru will come along and make a suggestion.

1. You could add a check in your code to see if ToDate is null or empty and set the details view mode to edit instead of insert
2. Trigger
3. a variant on 1 really, which checks the ToDate and change the view mode accordingly.

I vote #2 with a prompt that it will "close out" the previous one. But it depends on your business requirements.

if you go with a Trigger on update, remember that an update is actually a delete and insert.

CREATE TRIGGER dbo.SetClosed
ON dbo.Table_Name
FOR INSERT
AS
--do sql code here

I am using SQL Express 2005.
I found this 'scope_identity()' function on the web. It's supposed to grab the identity value from the last transaction. I don't think it's working yet.
CREATE TRIGGER [PositionFillInserted]
ON [dbo].[tblPositionFill]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE tblPositionFill
SET ToDate =
(SELECT FromDate FROM tblPositionFill
WHERE PositionFillID = scope_identity())
WHERE PersID =
(SELECT PersID FROM tblPositionFill
WHERE PositionFillID = scope_identity())
END
I take it the trigger doesn't respond to transactions CAUSED by the trigger itself?

ALTER TRIGGER [PositionFillInserted]
ON [dbo].[tblPositionFill]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE tblPositionFill
SET ToDate =
(SELECT FromDate FROM tblPositionFill
WHERE PositionFillID = @@identity)
WHERE PersID =
(SELECT PersID FROM tblPositionFill
WHERE PositionFillID = @@identity)
AND PositionFillID <> @@identity

ENDIs there anything else I should be thinking about?

@@Identity is a bad idea to use here because its' scope is database wide not table specific. Triggers can capture data from the altered row like this for example:

DECLARE @LastID int
Select @LastID=PositionFillID from Inserted

yes, that is literally "inserted" in place of a table name. Inserted is actually a "temp table" of sorts holding that record.

Where do I put the DECLARE statement? Up top?

Right after the "AS" statement and before BEGIN.
When you use DECLARE, it declares a local variable to that procedure just like in any programming environment. In VB we do DIM var.. in sql they do DECLARE var(type).

ALTER TRIGGER [PositionFillInserted]
ON [dbo].[tblPositionFill]
AFTER INSERT
AS
DECLARE @LastID int
Select @LastID=PositionFillID from Inserted
BEGIN
SET NOCOUNT ON;
UPDATE tblPositionFill
SET ToDate =
(SELECT FromDate FROM tblPositionFill
WHERE PositionFillID = @LastID)
WHERE PersID =
(SELECT PersID FROM tblPositionFill
WHERE PositionFillID = @LastID)
AND PositionFillID <> @LastID
AND ToDate = NULL

END
and that doesn't close out the old record, but no errors.
Should it be like this:
ALTER TRIGGER [PositionFillInserted]
ON [dbo].[tblPositionFill]
AFTER INSERT
AS
DECLARE @LastID int
@LastID=SELECT PositionFillID from Inserted
BEGIN
SET NOCOUNT ON;
UPDATE tblPositionFill
SET ToDate =
(SELECT FromDate FROM tblPositionFill
WHERE PositionFillID = @LastID)
WHERE PersID =
(SELECT PersID FROM tblPositionFill
WHERE PositionFillID = @LastID)
AND PositionFillID <> @LastID
AND ToDate = NULL

END

...No, I tried that second one that I posted, and it errors on execute.
I can't see what's wrong with the first statement that I posted, but it doesn't work.

you need to do
Set @LastID=(Select.......)
without Set, you will get an error.

Though, either way does the same thing. If @LastID is not being populated then there is something else that needs to be addressed. Triggers are a relatively new item on my plate as well.

try this

ALTER TRIGGER [PositionFillInserted]
ON [dbo].[tblPositionFill]
FOR INSERT
AS
DECLARE @LastID int
Set @LastID=(SELECT PositionFillID from Inserted)
BEGIN
SET NOCOUNT ON;
UPDATE tblPositionFill
SET ToDate =
(SELECT FromDate FROM tblPositionFill
WHERE PositionFillID = @LastID)
WHERE PersID =
(SELECT PersID FROM tblPositionFill
WHERE PositionFillID = @LastID)
AND PositionFillID <> @LastID
AND ToDate = NULL

END

I just tried it, and it didn't work. It didn't error on execute, either.
I'm travelling out to ND today for the week, don't know if I'll be on the forum (unless I can get internet connx at hotel).

Thanks for the help so far!

Here's what worked:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER TRIGGER [PositionFillUpdated]
ON [dbo].[tblPositionFill]
AFTER INSERT
AS
declare @LastID int
SELECT @LastID = (select PositionFillID from INSERTED)
BEGIN
SET NOCOUNT ON;
update tblpositionfill
set todate =
(select fromdate from tblpositionfill
where PositionFillID = @LastID)
where persid =
(select persid from tblpositionfill
where positionfillid = @LastID)
and
todate is null
and
positionfillid <> @LastID
ENDI've learned a lot about SQL in the last 24 hours.
nb: I didn't know I had the right answer for a while, as I was looking at the table IN SQL Management Studio Express 2005...and it does not refresh the whole table when you enter a record...the other rows are still a snapshot of the original recordset.

lol.. yeah, that can be confusing. Just remember, any time you execute to a new window, you have re-execute after any changes :)

Thanks again for all the help, Kodo.