DetailsView validation for INSERT command (1 Viewer)

Sergeant

Someone's gotta do it
Local time
Today, 10:27
Joined
Jan 4, 2003
Messages
638
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.
 

Kodo

"The Shoe"
Local time
Today, 10:27
Joined
Jan 20, 2004
Messages
707
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
 

Sergeant

Someone's gotta do it
Local time
Today, 10:27
Joined
Jan 4, 2003
Messages
638
Here's where I got to last night...

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.
Code:
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?
 

Sergeant

Someone's gotta do it
Local time
Today, 10:27
Joined
Jan 4, 2003
Messages
638
OK, I got it to work with this...

Code:
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
	
END
Is there anything else I should be thinking about?
 

Kodo

"The Shoe"
Local time
Today, 10:27
Joined
Jan 20, 2004
Messages
707
@@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.
 

Sergeant

Someone's gotta do it
Local time
Today, 10:27
Joined
Jan 4, 2003
Messages
638
Where do I put the DECLARE statement? Up top?
 

Kodo

"The Shoe"
Local time
Today, 10:27
Joined
Jan 20, 2004
Messages
707
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).
 

Sergeant

Someone's gotta do it
Local time
Today, 10:27
Joined
Jan 4, 2003
Messages
638
OK here's what I have now...

Code:
ALTER TRIGGER [PositionFillInserted] 
   ON  [dbo].[tblPositionFill] 
   AFTER INSERT
AS 
[B]DECLARE @LastID int 
Select @LastID=PositionFillID from Inserted[/B]
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:
Code:
ALTER TRIGGER [PositionFillInserted] 
   ON  [dbo].[tblPositionFill] 
   AFTER INSERT
AS 
[B]DECLARE @LastID int 
[COLOR="Red"]@LastID=SELECT PositionFillID from Inserted[/COLOR][/B]
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
 

Sergeant

Someone's gotta do it
Local time
Today, 10:27
Joined
Jan 4, 2003
Messages
638
...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.
 

Kodo

"The Shoe"
Local time
Today, 10:27
Joined
Jan 20, 2004
Messages
707
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
Code:
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
 

Sergeant

Someone's gotta do it
Local time
Today, 10:27
Joined
Jan 4, 2003
Messages
638
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!
 

Sergeant

Someone's gotta do it
Local time
Today, 10:27
Joined
Jan 4, 2003
Messages
638
Here's what worked:
Code:
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
END
I'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.
 
Last edited:

Kodo

"The Shoe"
Local time
Today, 10:27
Joined
Jan 20, 2004
Messages
707
lol.. yeah, that can be confusing. Just remember, any time you execute to a new window, you have re-execute after any changes :)
 

Users who are viewing this thread

Top Bottom