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."

 

[ousoonerjoe]

Try something along these lines:

DECLARE @TimeIN DATETIME

SET @TimeIN = SELECT TimeIn FROM Table WHERE ....

IF @TimeIN IS NULL
(
     UPDATE statement)
ELSE(
     INSERT statement)
END

"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]

Okay, I created a trigger as follows:

CREATE TRIGGER timeclock ON [dbo].[tbl_timeclock]
FOR INSERT
AS
DECLARE @TimeIN DATETIME,
@EE numeric(9,2)

Select @TimeIN = TimeIn FROM tbl_timeclock WHERE timein is null and employeenumber = @EE
begin
IF @TimeIN IS NULL
UPDATE tbl_timeclock set timein = getdatE()
ELSE
INSERT tbl_timeclock (employeenumber) values (@EE)
end

But this merely updated the TIMEIN column to be getdate() at the same time it input the TIMOUT. Did I miss something?


"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."

 

[bborissov]

You miss TWO major things:

1. Trigger is fired after ALL the job is done. That means you need to program your trigger that way so it can handle LOTS of records, not just one.

2. You declare @EE but you never set its value, so it is NULL and your SELECT not works.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[LloydDobler]

Thanks Borislav. #2, oops, i've set it to be:
select @EE = employeenumber from inserted
same results.

I'm not really sure what you mean about #1 though.

"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."

 

[bborissov]

#1 means that you may have several records in INSERTED table not just one.
If you did something like:

INSERT INTO YourTable
SELECT 1
UNION
SELECT 2
UNION
SELECT 3

INSERTED table will have ALL 3 records when the TRIGER is fired, so you must program your trigger that way so it can handle different records in the INSERTED table.

And please don't tell me "I'm always insert one record at a time" for you and your application that can be true but what if some DBA or someone else do something out of your application? Some maintenance, maybe?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[LloydDobler]

wow, I'm very confused as to what you're saying. What is this 'select 1/2/3' all about? I also don't really understand why I'd use a union statement? And as for 'don't tell me "I'm always insert one record at a time"', I guess I'm not following that, I am trying to insert one at a time, however, there could be multiple people trying to enter records at the same time, yes, but it's one record at a time, not several at one time.

"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]

Lloyd-

What bborissov is saying is you need to account for all possible ways to update the data. While the application may only allow one at a time, anyone with SQL knowledge and access can login and INSERT a batch of records at a single instance. This Trigger will only update one record from that batch. He is trying to show you how to make the Trigger account for Single use as well as a Multi-Record INSERT.

"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]

Okay, but I don't want to insert multiple records. Is this a difficult thing to do based on my table structure. I thought it'd be pretty simple, but clearly I'm wrong. If (based on table above) say EMP# 4 goes to clock in/out, it will update record # 2's TIMEIN field. If say EMP# 99 goes to clock in/out, it will input a new 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."

 

[bborissov]

There is nothing about that. This is just an example how you cold insert many records at a time.

What I meant was that you can design your application to insert ONE record at a time (no matter how many users can do this at the same time), but you NEVER be sure that you covered ALL possibilities. Some DBA that has access to your DB and has Management Studio or EM can insert 5 or 10 or 100 records in the same batch, then your trigger will fail because it is not programmed to handle many records.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[ousoonerjoe]

It's about planning for the "unforeseen" events. Rather than creating a loop hole that can ignore this constraint, close it up before it ever causes an issue for you. You have the Trigger written to work for your purpose. It just has a loop hole in it that may cause you issues on down the road. You can control users, it's all those other people with accesses you can't watch all the time.

"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]

You have the Trigger written to work for your purpose".

No I don't, it didn't work at all, it updated the TIMEIN column when I didn't want it to, and left the TIMEIN field blank in previous row when I wanted it to be updated. I've gotten absolutely nowhere but frustrated.

"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]

almost sounds like your IF statement is backwards.

Post what you have up to this point.

"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]

CREATE TRIGGER timeclock ON [dbo].[tbl_timeclock]
FOR INSERT
AS
DECLARE @TimeIN DATETIME,
@EE numeric(9,2)

select @EE = employeenumber from inserted
Select @TimeIN = TimeIn FROM tbl_timeclock WHERE timein is null and employeenumber = @EE
begin
IF @TimeIN IS NULL
UPDATE tbl_timeclock set timein = getdatE() where tbl_timeclock.employeenumber = @EE
ELSE
INSERT tbl_timeclock (employeenumber) values (@EE)
end


"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]

If the trigger still looks the same, it probably updated the TIMEIN column for ALL records since you don't give the UPDATE statement a WHERE clause.

UPDATE tbl_timeclock set timein = getdate() [COLOR=red]WHERE employeenumber = @EE[/color]

Let's get the trigger working for your case and then we'll work on the education (you WILL want to write the trigger for multiple updates...they aren't lying).

 

[ethorn10]

Type...too....slow...

sorry

 

[LloydDobler]

yes, it update all records, but I do have a where statement in my update statement.

"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 need to tighten the WHERE clause a little more...

WHERE employeenumber = @EE AND RecNumber = inserted.RecNumber

"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]

still inserted a new record instead of updating TimeIN of older record (that's missing TimeIN). It didn't update all records though, so that's a plus.

"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]

Scroll down to where it talks about INSTEAD OF.
MSDN : CREATE Trigger
Replace FOR INSERT with INSTEAD OF INSERT. This should intercept the insert statement and replace it with the UPDATE statement if that is what is needed.

NOTE: This is not tested just typed.

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