Update All Using Condition Row By Row

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update All Using Condition Row By Row

Author

Topic

asif372
Posting Yak Master

100 Posts

Posted - 2011-02-24 : 13:58:11

Basically I Am Developing Attendance System data from Attendance Machine I Get In First In Second Out Third In Fourth Out condition just like this
(UserID) (CheckTime) (Checktype(In or Out))
(1) (10/18/2010 9:39:08 AM) (I)
(1) (10/18/2010 6:12:46 PM) (o)
(1) (10/18/2010 6:12:47 PM) (I)
(1) (10/19/2010 8:24:13 AM) (O)
(1) (10/19/2010 8:24:13 PM) (I)

i have developed a Delete Trigger when any Data Deleted it should fire and update Checktype All > deleted checktime

Create Trigger [dbo].[DeleteCheck]
On [dbo].[CHECKINOUT]
After Delete
AS
BEGIN
Declare @CtypeULess varchar(1);
Declare @Ctype varchar(1);
Declare @CUtype varchar(1);
DECLARE @Date Datetime;
DECLARE @ID int;
SET @ID =(select USERID from Deleted);
SET @CtypeULess= (select CHECKTYPE from Deleted);
SET @Date = (Select CheckTime from Deleted);
SET @Ctype =(SELECT Top 1 CheckTypeUpdated FROM CHECKINOUT where USERID = @Id and ORDER BY CHECKTIME DESC);
if(@Ctype='I')
SET @CUtype='O'
Else if (@Ctype='O')
SET @CUtype='I'
Else
SET @CUtype='I'
Insert Into CheckInOutDeleted Values(@ID,@Date,@CUtype,'a')
update CheckInOut Set CheckTypeUpdated=@CUtype Where USERID=@ID and CHECKTIME >(@ID)
End

It Check Condition One Time and Then Update All Records I Want Him To Check Condition Row By Row And Then Update CheckType

any ideas????
Thanks In Advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-02-26 : 09:29:31

i think you should join to deleted table based on pk column for repeating it for each record. this will make sure it works fine for bulk updates too.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-02-26 : 15:20:12

Basically I Am Developing Attendance System data from Attendance Machine I Get In First In Second Out Third In Fourth Out condition just like this <<

Sorry about the hardware not giving your data in proper ISO formats. Most systems today can do this, so you might want to upgrade. But for posting on a newsgroup, let's clean up the data.

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

>> I want to developed a Delete Trigger when any Data Deleted it should fire and update All check_type > deleted check_time where user_id=Deleted .. I have Developed a Trigger but not working as per my need <<

Good SQL programmers avoid procedural code like it was cannibalism. Also, tell us what you want to do and not how you have decided to do it. You will get better answers.

We have DRI actions in SQL, so we don't want or need triggers. We model time in durations. Your code is loaded with reserved words like “date”, vague names like “id” and assembly language flags. Why did you write VARCHAR(1)? Think how silly this is. You confuse rows and records.

In short, you approach to SQL is completely wrong.

CREATE TABLE Timecards
(user_id INTEGER NOT NULL
REFERENCES Users (user_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
check_in_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
PRIMARY KEY (user_id, check_in_time)
check_out_time DATETIME, –- null is still open
CHECK (check_in_time < check_out_time));

CREATE TABLE Users
(user_id INTEGER NOT NULL PRIMARY KEY,
..);

INSERT INTO Users (user_id, ..)
VALUES (1, ..);

INSERT INTO Timecards
VALUES
(1, '2010-10-18 09:39:08', '2010-10-18 18:12:46'),
(1, '2010-10-18 18:12:47', '2010-10-19 08:24:13'),
(1, '2010-10-19 20:24:13', NULL);

When you delete a user, all his rows will be deleted by the CASCADE. No procedural code needed.

However, you need a procedure to update an existing row where “check_out_time IS NULL” and insert the next row with the check_in_time. Try writing it with one MERGE statement. All of this should run about 10 to 100 times faster and safer than your code.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources