The delete trigger is not updating the field to current Date/Time??? 3

[zeeshan13]

Hi all,

I have a table called Session which holds the session information of each user who logins to our internal non-web based application.
As soon as the user logout the Session table removes the record for that user. So the user records is only active in to the session table as long as the user is active.

The Session table has the following fields with thier data type:

UserID(uniqueidentifier),SessionID(uniqueidentifier),Spid(int),StartTime(DateTime),Duration(DateTime),ActivitySID(uniqueidentifier),Location(varchar),LanguageID(int),UserDomain(nvarchar),UserID(nvarchar),FirstName(nvarchar),LastName(nvarchar),IsAdminuser (bit)

The SessionID is the PrimaryKEY.

Now, I need to keep track of users who login anytime in to the application.

To start with, I created a insert trigger on Session table, which insert a new record into a table UserTrack , whenever there's an insert on Session table. It inserts all fields into the UserTrack table for that new user.

The following trigger for this purpose works fine:

--Insert trigger
create Trigger InsertUser
on Session
For insert
as
if update(SessionSID)
insert into UserTrack
select * from inserted
--End of insert trigger


Now as mentioned before the Session table keep records only as long as the user is active & as soon as the user logout the record is deleted .

Now, finally I want to create a delete trigger on the same Session table.
That is, when a user logsout (which mean, when a record is deleted from Session table) update the UserTrack table, set the Duration (datetime type) field to current time, where ofcourse the Session.SessionSID=deleted.SessionSid


For this purpose, I created this Trigger:

--Delete trigger
create Trigger InsertLogoutTimeForUser
on Session
For Delete
as
if update(SessionSID)
update UserTrack
set UserTrack.Duration=getdate()
from UserTrack join Deleted
on (UserTrack.SessionSID=deleted.SessionSID)
--End of Delete trigger

When, I am quering the UserTrack table the Duration is not updating.

Any idea?.

Thanks a million in advance...



Looking for a quick response.

Thanks,

 

[jbenson001]

This is from books on line:

IF UPDATE (column)

Tests for an INSERT or UPDATE action to a specified column and is not used with DELETE operations.

And, since you are deleting the row, you are not updating the SessionSID anyway.

Jim

 

[zeeshan13]

jbenson001 ,

So what should I use for delete???

Thanks a million in advance...

 

[jbenson001]

You can use the trigger as you have it, just remove the IF Updated() statement.

--Delete trigger
create Trigger InsertLogoutTimeForUser
on Session
For Delete
as 
   update UserTrack
   set UserTrack.Duration=getdate()
   from UserTrack join Deleted
   on (UserTrack.SessionSID=deleted.SessionSID)
--End of Delete trigger

Jim

 

[zeeshan13]

jbenson001,
Thanks for you help.

I got it.
Here's the correct Delete Trigger:

--Delete trigger
create Trigger InsertLogoutTimeForUser
on Session
For Delete
as
update UserTrack
set UserTrack.Duration=getdate()
from UserTrack join Deleted
on
(UserTrack.SessionSID=deleted.SessionSID)

 

[SQLDenis]

This should be all you need

create Trigger InsertLogoutTimeForUser
on Session
For Delete
as 
if @@ROWCOUNT =0
return
update u
set u.Duration=getdate()
from UserTrack u join Deleted d
on (u.SessionSID=d.SessionSID)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

Nevermind, Jim beat me to it

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[jbenson001]

Great.. Glad you got it working...

Jim

 

[zeeshan13]

SQLDenis,

Although, my trigger worked. I am just curiours, how your trigger works.

create Trigger InsertLogoutTimeForUser
on Session
For Delete
as
if @@ROWCOUNT =0
return
update u
set u.Duration=getdate()
from UserTrack u join Deleted d
on (u.SessionSID=d.SessionSID)

I guess in your case, it is going to check if @@ROWCOUNT =0, meaning if there's a recent change on the Session table or not. If there's no change, then don't anything, otherwise update the Duration field in the UserTrack table.

Please let me know, if I am correct.
And if possible let me know, which method is fast & more productive.

Thanks in advance....

 

[SQLDenis]

if @@ROWCOUNT =0
don't do anything and exit immediately (not wasting resources)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[zeeshan13]

SQLDenis Thanks again.

You are great

 

[jbenson001]

Thanks for the starts guys ..