September 27, 2006 at 2:06 am
Hello everyone,
i m using SqlServer 2000 and i have 2 tables :
The 1st Table is : "UserActivityLog": (With following column names)
SessionID
UserActivityCode
LogUserTypeID
SystemUserID
BuyerID
SupplierID
AccessInterfaceID
CallerID
IPAddress
The 2nd Table is : "SessionOnline" (With following column names)
StartDate
LastActivityTime
SessionID
UserActivityCode
LogUserTypeID
SystemUserID
BuyerID
SupplierID
AccessInterfaceID
CallerID
IPAddress
now i want to write a Trigger (Before), After insertion in the first table the inserted values
automatically goes to the 2nd table."SessionOnline" but for the specific SessionID.
e.g. if i make some new insertion in table 1 and write sessionID =1 for 1st insertion after insertion , i make
new insertion again in the table1 and for the same sessionID =1 then my 2nd table updates with the new
one entry.
As for 1st table i made 3 insertion for sessionID=1 then in my 2nd table the last inserted value goes to the 2nd
table. (Each time my 2nd table Updated if i make new insertion for the same sessionID) coz in 2nd table
for each sessionID i have only one entry in the 2nd Table "SessionOnline".
plz reply me asap
its very urgent.plzzzzzzzzz
September 27, 2006 at 3:47 am
Hello,
what is the question, please?
As far as I can understand your explanation, I would probably go about it the other way... insert or update table2 and have a trigger that will write the log (always inserts a new row in table1). If the direction is something you can't influence, then the trigger needs to consist of (at least) 2 statements :
first, update existing rows;
second, insert non-existing rows.
You didn't mention what happens in table1 when session is terminated, but obviously you should also register that somehow and delete the corresponding row from table "Session online"...?
September 27, 2006 at 11:45 am
Can u give us specific examples cos that would convey what u want done.
Thanks
Sreejith
September 27, 2006 at 11:31 pm
Hello Vladan u right i ned 2 stmts (for writing a Log activit) for update n insert also . At that time i dont want to delete the session ID n dont make any change in the table 1 if session terminated.
as i have no exp how to write trigger in sql server so me try to write a SP for this , here is the code u plz check this is it okay or not?
as i think its not so good , plz check this n give me some solution as i m stuck in this problem.
CREATE PROCEDURE Test
@SessionID varchar(64),
@UserActivityCode int,
@LogUserTypeID int,
@LogUserID int,
@AccessInterfaceID int,
@CallerID int,
@IPAddress varchar(15) ,
@LogData varchar(500)
AS
-- declare 3 local variables
declare @SystemUserID int
declare @BuyerID int
declare @SupplierID int
set @SystemUserID = null
set @BuyerID = null
set @SupplierID = null
-- set appropriate variable
if @LogUserTypeID = 1
set @SystemUserID = @LogUserID
else if @LogUserTypeID = 2
set @BuyerID=@LogUserID
else
set @SupplierID=@LogUserID
insert into UserActivityLog
([LogDate],
[SessionID],
[UserActivityCode],
[LogUserTypeID],
[SystemUserID],
[BuyerID]
[SupplierID],
[AccessInterfaceID],
[CallerID],
[IPAddress],
[LogData])
VALUES
(getdate(),
@SessionID,
@UserActivityCode,
@LogUserTypeID,
@SystemUserID,
@BuyerID,
@SupplierID,
@AccessInterfaceID,
@CallerID,
@IPAddress,
@LogData)
If Exists
(
select [SessionID]
from SessionsOnline
where [SessionID] = @SessionID
)
BEGIN
-- set appropriate variable
if @LogUserTypeID = 1
set @SystemUserID = @LogUserID
else if @LogUserTypeID = 2
set @BuyerID=@LogUserID
else
set @SupplierID=@LogUserID
Update SessionsOnline
SETUserActivityCode=@UserActivityCode ,LogUserTypeID=@LogUserTypeID,AccessInterfaceID=@AccessInterfaceID,CallerID=@CallerID,IPAddress=@IPAddress
WHERE SessionID = @SessionID
END
ELSE
BEGIN
-- set appropriate variable
if @LogUserTypeID = 1
set @SystemUserID = @LogUserID
else if @LogUserTypeID = 2
set @BuyerID=@LogUserID
else
set @SupplierID=@LogUserID
INSERT INTO SessionsOnline
([StartDate],
[LastActivityTime],
[SessionID],
[UserActivityCode],
[LogUserTypeID],
[SystemUserID],
[BuyerID],
[SupplierID],
[AccessInterfaceID],
[CallerID],
[IPAddress])
VALUES
(getdate(),
getdate(),
@SessionID,
@UserActivityCode,
@LogUserTypeID,
@SystemUserID,
@BuyerID,
@SupplierID,
@AccessInterfaceID,
@CallerID,
@IPAddress)
END
GO
September 29, 2006 at 5:30 am
I would suggest to use a trigger, but since I still don't know much about how it should work, I'll show you a simplified one, without any validity checks etc. It works on assumption, that:
- there are no updates ocurring in the table UserActivityLog - if something changes, a new row is inserted with the same SessionID
- values from UserActivityLog can be brought to SessionOnline directly, without any need to modify them (the manipulation based on LogUserTypeID happen before the row is inserted into UserActivityLog)
- you don't care about sessions that have ended - once a session starts, it will remain in SessionOnline table forever and just be updated with new values
- what I'm putting into the date columns is what I think should be there...
If it doesn't work as expected, let us know what is the problem and post sample data so that we can test it.
/*table structure (testing environment)*/
CREATE TABLE UserActivityLog
(
SessionID int,
UserActivityCode varchar(10),
LogUserTypeID int,
SystemUserID int,
BuyerID int,
SupplierID int,
AccessInterfaceID int,
CallerID int,
IPAddress varchar(30)
)
CREATE TABLE SessionOnline
(
StartDate datetime,
LastActivityTime datetime,
SessionID int,
UserActivityCode varchar(10),
LogUserTypeID int,
SystemUserID int,
BuyerID int,
SupplierID int,
AccessInterfaceID int,
CallerID int,
IPAddress varchar(30)
)
/*This is the trigger - for insert only*/
CREATE TRIGGER write_session ON UserActivityLog
AFTER INSERT
AS
/*update existing rows*/
UPDATE SessionOnline
SET LastActivityTime=GETDATE(), UserActivityCode = i.UserActivityCode, LogUserTypeID = i.LogUserTypeID,
SystemUserID = i.SystemUserID, BuyerID = i.BuyerID, SupplierID = i.SupplierID, AccessInterfaceID = i.AccessInterfaceID,
CallerID = i.CallerID, IPAddress = i.IPAddress
FROM inserted i
JOIN SessionOnline s ON s.SessionID=i.SessionID
/*insert missing rows*/
INSERT INTO SessionOnline (StartDate,LastActivityTime,SessionID,UserActivityCode,LogUserTypeID,
SystemUserID,BuyerID,SupplierID,AccessInterfaceID,CallerID,IPAddress)
SELECT GETDATE(), GETDATE(), i.SessionID, i.UserActivityCode, i.LogUserTypeID, i.SystemUserID,
i.BuyerID, i.SupplierID, i.AccessInterfaceID, i.CallerID, i.IPAddress
FROM inserted i
LEFT JOIN SessionOnline s ON s.SessionID=i.SessionID
WHERE s.SessionID IS NULL /*this makes sure you only insert if session does not exist*/
GO
/*inserting dummy test data*/
INSERT INTO UserActivityLog (SessionID, UserActivityCode, LogUserTypeID,SystemUserID,
BuyerID,SupplierID,AccessInterfaceID,CallerID,IPAddress)
SELECT 1, 'CX', 1, 123, NULL, NULL, 456, 789, '100.1.1.6'
INSERT INTO UserActivityLog (SessionID, UserActivityCode, LogUserTypeID,SystemUserID,
BuyerID,SupplierID,AccessInterfaceID,CallerID,IPAddress)
SELECT 1, 'CQ', 1, 123, NULL, NULL, 456, 789, '100.1.1.6'
/*see what happened*/
select * from UserActivityLog
select * from SessionOnline
/*cleanup*/
--DROP TABLE UserActivityLog
--DROP TABLE SessionOnline
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply