update record if field is null, else insert 1

[LloydDobler]

Hi all, hopefully an easy one...First, here's my Table

Rec# EMP# timeOUT timeIN
1 4 11:30 12:05
2 4 12:30 --
3 9 11:05 11:15
4 10 11:45 ---

What I'd like to do is when inserting a new record, to check if there's an existing record for that EMP that doesn't have a timeIN. If timeIN is null, then timeIN should be updated (=getdate()). If there are no records missing timeIN, then insert a new timeOUT record.

"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."

 

[LloydDobler]

nope, it instead didn't insert or update, just sat there. When I refreshed, the data I put it went away.

"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."

 

[ousoonerjoe]

If i've understood your question properly this should get you to where you need to be or at least on the right track. (This is for a single INSERT (not batch):

ALTER TRIGGER trg_TimeCard ON TimeCard
FOR INSERT

AS

DECLARE @TimeIN DATETIME
DECLARE @TimeOut	DATETIME
DECLARE @EmpID	INT
DECLARE @RecID	INT

SET @EmpID = (SELECT EmpID FROM Inserted);
SET @RecID = (SELECT TimeID 
				FROM TimeCard 
				WHERE TimeIn IS NULL AND 
					EmpID = @EmpID AND 
					TimeID < (SELECT TimeID FROM Inserted));
SET @TimeIN = (SELECT TimeIn FROM TimeCard WHERE TimeIn IS NULL AND EmpID = @EmpID AND TimeID = @RecID);
SET @TimeOut = (SELECT TimeOut FROM Inserted)

BEGIN

IF @TimeIN IS NULL AND @TimeOut IS NULL
    UPDATE TimeCard 
	SET TimeIn = GETDATE()
	WHERE TimeID = @RecID
END
GO

@RecID - gathers the previous RecID that had a NULL for that Employee.
@EmpID - Pulls the employee that is being updated and needs to be checked.
@TimeIN - Pulls the value to check against.
@TimeOut - Pulls the value to check against. If NULL then TimeOut is not being UPDATEd in this INSERT.

If anyone cares to improve on this, they are more than welcome to (Coffee hasn't kicked in yet, and know there's a prettier way to do this.).


"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[LloydDobler]

That code just inserted a new record, didn't update the previous. Did it work for you?

"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."

 

[SgtJarrow]

To throw another set of eyes on this problem....

Is a TRIGGER really the best way to go??

The original problem was:
If a record exists with a NULL time field, update that field...
Else add a new record

This seems to be a timeclock system. So the OP is looking to use one "clockin/out" button. If the employee is currently clocked in and they hit the button, enter the time as a clockout time of their latest record (which should have no clock out time). If the employee is clocked out, then create a new record and enter the time in the clock in field.

Of course, the OP has not mentioned anything about how to handle times that span over the midnight hour....but let's not go there yet.


My question is "Is a TRIGGER really the best way to handle this?" It seems to me that by the time the TRIGGER is kicked off, a record has been added or some such. I am not a TRIGGER expert, so forgive me if I am wrong.

I think this might better be handled in the application logic and not in the database side. Or at the very least the timing seems wrong for a TRIGGER.

Please enlighten me if I am wrong.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[ousoonerjoe]

yes. Make sure you have adjusted all table/column names to match your structure.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[LloydDobler]

SoonerJoe, I changed all tables/columns, still nothing. I even created a table similar to yours, same thing. Just inserts a new row without updating the previous TimeIN for that EMP.


"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."

 

[ethorn10]

For starters, go back and read up on INSTEAD OF triggers. If you're sending an INSERT statement from your application, this trigger is firing AFTER the insert. If you're doing the INSERT from a stored procedure, why not have the stored procedure determine the logic of UPDATE/INSERT (similar to what mstrmage1768 suggested except the application still doesn't know the status of timein/timeout)?

 

[LloydDobler]

I have no idea of how to make a stored procedure determine the logic of the UPDATE/INSERT.

"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."

 

[ousoonerjoe]

I don't know what more to tell you at this point. I have created a table that matched identically to your original posting. Created a TRIGGER that Updated the record and Inserted as needed. Posted my findings.

All i can say is to make sure the variables are being populated like they are supposed to. That is where I had a hick-up in my testing. I added another column to the test table VARCHAR(10) and had the UPDATE place the results of each variable in it. Once i verified all the variables i put them back in place and put the IF statement back in and all worked perfect.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[ethorn10]

INSTEAD OF trigger. ousoonerjoe provided a link.

 

[SgtJarrow]

ousoonerjoe,

Thanks for the info on the INSTEAD OF. New to me and I learned from it. Have a star.



Lloyd,

ousoonerjoe's info on INSTEAD OF should work as far as I can tell. But you could also go the route of the stored procedure.

Your stored procedure would look similar like your TRIGGER. Only instead of the TRIGGER firing after the INSERT, the stored procedure will determine whether to update or insert.

I think the logic provided by ousoonerjoe is pretty much correct (haven't tested anything). You just need to work with it to make sure you have the field names correct and test, test, test.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[ousoonerjoe]

Thank you for the star, Mage.

Lloyd-
Given the problems and logic you are encountering, I have to agree that this would best be served in a Stored Proc or the application itself. It makes more since to control the data going out rather than intercepting and trying to control it then. Since you are dealing with people's activities, I am guessing this is related to either security or payroll. With either of those, you need to be very specific with times. Supervisors/security logs need missing entries to be updated with a more accurate time. They also should be notified about the laps. (but that may just be my experience with gov talking)

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[LloydDobler]

Not really a secure thing, funny enough. Trying to capture times away from work (Length of Loafing for Ken Henderson fans). Anyway, I've changed to go sproc route, and have it working (with a set variable). So now I just need to build my app to pass the variable and I should be good to go. Thanks to all, I'll pass some stars after I get this F%*#@G thing working. Here's my sproc code:

CREATE PROCEDURE spTimeCard
(@EMP int)
AS 
if exists(select * from timecard where TimeIN is null and EMPid = @EMP) 
begin 
UPDATE timecard SET timeIN = getdate() WHERE (empid = @emp) and TimeIN is null
end 
else 
begin 
INSERT INTO timecard(empid) VALUES (@EMP); 
SELECT empID  FROM timecard WHERE (empID = @@IDENTITY) 
end
GO

"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."

 

[ousoonerjoe]

Just one thing. The Sproc you have posted here will not work.

SELECT empID  FROM timecard WHERE [b](empID = @@IDENTITY) [/b]

EmpId is not the Identity field of the table. TableID is. @@IDENTITY will return the value for the field with the IDENTITY in this case TimeID (aka RecID).

Also, why are you returning the value that you input to the Stored Proc? If you don't need it, drop the SELECT. If you do need it, you already have it from when you passed the argument to the proc in the first place. Is this supposed to be returning the TimeID/RecID instead?



"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[SQLSister]

Also for future use, you almost never want to use @@identity anyway as it can give wrong results if anyone puts a trigger on the table. If you need to know the identity value you just inserted into a table, use scope_identity() instead.

"NOTHING is more important in a database than integrity." ESquared