Trying to convert Oracle Trigger to MS SQL
-
Hi all,
I'm a little lost with this one. Basically I have a table that only keeps "current" login information, and I need to use that to create a history table by timestamping the login time when the data is inserted into the table, and timestamp the logout time when the data is deleted from the table. What has complcated things is the use if 'FOR EACH ROW' which I think means I need to use a trigger. I have tried converting the Oracle Trigger, but I don't understand what I've done wrong either in the construct or the syntax.
------Oracle Trigger---------
CREATE OR REPLACE TRIGGER "modify_sessions_history"
AFTER INSERT OR DELETE ON "rep_sessions"
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO rep_sessions_history
VALUES(:new.ses_oid, SYSDATE, NULL,
(SELECT acc_loginname FROM rep_accounts WHERE acc_oid = :new.ses_acc_oid),
:new.ses_server_ipaddress, :new.ses_client_ipaddress, NULL);
ELSIF DELETING THEN
UPDATE rep_sessions_history
SET sh_logout_date = SYSDATE
WHERE sh_ses_oid = :old.ses_oid;
END IF;
END;
-----My attempt at converting to MS SQL----------------
CREATE TRIGGER [modify_sessions_history]
AFTER INSERT,DELETE ON [dbo].[rep_sessions]
AS
/* N Chapman 21-01-2006 for MS SQL 2000 */
/* Keeps a history of Named and Concurrent license use in OVSD */
/* REP_SESSIONS_HISTORY (non-standard OVSD table) is updated */
/* The system time is used to timestamp (not the person location) */
/* when the person logs on or off */
--INSERTED Table Code
Declare @ses_oid INT
Declare @login_date DATETIME
Declare @logout_date DATETIME NULL
Declare @login_name VARCHAR(32)
Declare @show_name VARCHAR(32)
Declare @concurrent_user BIT
Declare @named_user BIT
Declare @acc_tmz_oid VARCHAR(32)
Declare @server_ip VARCHAR(64)
Declare @client_ip VARCHAR(64)
Declare @session_count INT
-- No output to screen
set nocount on
IF (SELECT ses_acc_oid FROM INSERTED IS NOT NULL)
-- Open the cursor to process possible multiple updates
Declare Cursor_Insert CURSOR FORWARD_ONLY FOR
SELECT ses_acc_oid FROM INSERTED, GETDATE(), NULL,
(SELECT acc_loginname, acc_showname, acc_concurrentuser, acc_nameduser, acc_tmz_oid FROM rep_accounts WHERE acc_oid = INSERTED.ses_acc_oid),
INSERTED.ses_server_ipaddress, INSERTED.ses_client_ipaddress, NULL)
OPEN Cursor_Insert
-- Start retreiving the Inserted lines
Fetch Next from Cursor_Insert
into @ses_oid, @login_date, @logout_date, @login_name, @show_name, @concurrent_user, @named_user, @acc_tmz_oid, @server_ip, @client_ip, @session_count
while @@Fetch_status=0
BEGIN
UPDATE REP_SESSIONS_HISTORY
set sh_ses_oid = @ses_oid
set sh_login_date = @login_date
set sh_logout_date = @logout_date
set sh_login_name = @login_name
set sh_show_name = @show_name
set sh_concurrent_user = @concurrent_user
set sh_named_user = @named_user
set sh_server_ip = @acc_tmz_oid
set sh_server_ip = @server_ip
set sh_client_ip = @client_ip
set sh_session_count = @session_count
Fetch Next from Cursor_Insert
into @ses_oid, @login_date, @logout_date, @login_name, @show_name, @concurrent_user, @named_user, @acc_tmz_oid, @server_ip, @client_ip, @session_count
End
-- No more so close the cursor and deallocate it.
CLOSE Cursor_Insert
DEALLOCATE Cursor_Insert
--DELETED Table Code
IF (SELECT ses_acc_oid FROM DELETED IS NOT NULL)
Declare Cursor_Delete CURSOR FORWARD_ONLY FOR
(SELECT ses_acc_oid FROM REP_SESSIONS_HISTORY WHERE sh_ses_oid = DELETED.ses_oid), GETDATE()
OPEN Cursor_Delete
-- Start retreiving the Deleted lines
Fetch Next from Cursor_Delete into @ses_oid, @logout_date
while @@Fetch_status=0
BEGIN
UPDATE REP_SESSIONS_HISTORY
set sh_ses_oid = @ses_oid
set sh_logout_date = @logout_date
Fetch Next from Cursor_Delete into @ses_oid, @logout_date
End
-- No more so close the cursor and deallocate it.
CLOSE Cursor_Delete
DEALLOCATE Cursor_Delete
----------------------------------------------------------
Should I just start again? Any pointers appreciated.
-
A SQL trigger that contains a cursor, where the cursor is based on a SELECT statement that has a sub-SELECT within the main SELECT is a recipe for performance disaster.
This needs to be thrown away and re-written as a set-based operation. What are the requirements/business rules around inserts/deletes to the rep_sessions table ?
Also, since the behaviour of INSERTS and DELETEs appears to be different, why try to merge the different functionality into 1 trigger ? Create an INSERT trigger, that is set-based, concise and specific to whatever needs to happen on an INSERT. And create a 2nd DELETE trigger.
-
Thanks PW. I had a feeling splitting the trigger was the way to go. I had contemplated a SET based approach but fear I gone off course. The application keeps a track of who is currently logged on in rep_sessions, but not a history, hence the trigger(s) and additional table (rep_sessions_history). The idea is to make it possible to track who has been logged onto the system, and for how long.
I'll have another go at this. Once again, thanks for your feedback.
-
Howdy,
I've had another go at this and have discovered that I only needed a delete trigger. The syntax and all checks out alright, but the REP_SESSIONS_HISTORY tables isn't updating with any data.
---------------------Start Code-------------------
CREATE TRIGGER [sessions_history] ON [dbo].[rep_sessions]
FOR DELETE
AS
/* Made for MS SQL 2000 */
/* Keeps a history of Named and Concurrent license use */
/* REP_SESSIONS_HISTORY is updated */
/* The system time is used to do the timestamping (not the person location time) */
/* when the person logs off. */
/* -Part 1 : Update the Session History if there's only 1 session that's ended */
IF @@ROWCOUNT = 1
BEGIN
UPDATE REP_SESSIONS_HISTORY
set sh_ses_oid = (SELECT SES_OID FROM DELETED),
sh_ses_created = (SELECT SES_CREATED FROM DELETED),
sh_ses_end =GETDATE(),
sh_ses_acc_oid = (SELECT SES_ACC_OID FROM DELETED),
sh_ses_server_ipaddress = (SELECT SES_SERVER_IPADDRESS FROM DELETED),
sh_ses_client_ipaddress = (SELECT SES_CLIENT_IPADDRESS FROM DELETED),
sh_ses_isconcurrent = (SELECT SES_ISCONCURRENT FROM DELETED)
END
ELSE
/* -Part 2 : Update the Session History if there are multiple sessions that simultaneously end */
--DELETED Table Variables
Declare @ses_oid decimal (18,0)
Declare @login_date DATETIME
Declare @logout_date DATETIME
Declare @login_id decimal (18,0)
Declare @server_ip nvarchar (255)
Declare @client_ip nvarchar (255)
Declare @concurrent_user decimal (1,0)
-- No output to screen
set nocount on
-- Open the cursor to process possible multiple updates
Declare Cursor1 CURSOR FAST_FORWARD FOR
SELECT ses_acc_oid FROM DELETED
OPEN Cursor1
-- Start retreiving the Inserted lines using the Account Session ID
Fetch Next from Cursor1 into @ses_oid
while @@Fetch_status=0
BEGIN
set @ses_oid = (SELECT SES_OID FROM DELETED WHERE SES_OID = @ses_oid)
set @login_date = (SELECT SES_CREATED FROM DELETED WHERE SES_OID = @ses_oid)
set @logout_date = GETDATE()
set @login_id = (SELECT SES_ACC_OID FROM DELETED WHERE SES_OID = @ses_oid)
set @server_ip = (SELECT SES_SERVER_IPADDRESS FROM DELETED)
set @client_ip = (SELECT SES_CLIENT_IPADDRESS FROM DELETED)
set @concurrent_user = (SELECT SES_ISCONCURRENT FROM DELETED WHERE SES_OID = @ses_oid)
UPDATE REP_SESSIONS_HISTORY
set sh_ses_oid = @ses_oid,
sh_ses_created = @login_date,
sh_ses_end = @logout_date,
sh_ses_acc_oid = @login_id,
sh_ses_server_ipaddress = @server_ip,
sh_ses_client_ipaddress = @client_ip,
sh_ses_isconcurrent = @concurrent_user
Fetch Next from Cursor1
into @ses_oid
End
-- No more so close the cursor and deallocate it.
CLOSE Cursor1
DEALLOCATE Cursor1
----------------End Code----------------
Is it the Code? Is it something else? I have made sure that data types between the 2 tables match exactly, so I'm not sure where to go from here..
NC
-
Oh boy. Where to start. That is wrong on so many levels.
This is essentially the body of your Delete trigger. Just fill in the column names:
-- Update details for deleted records where the login_id is already in the history table
UPDATE H
SET sh_ses_oid = D.SES_OID,
sh_ses_created = D.SES_CREATED
{etc - you type the rest of the columns}
FROM REP_SESSIONS_HISTORY As H
INNER JOIN deleted As D
ON (H.login_id = D.login_id)
-- Also handle the case of login_id that is not in the history table yet
INSERT INTO REP_SESSIONS_HISTORY
( [You type the column list}
SELECT D.Column1, D.Column2, .... D.ColumnN
FROM deleted As D
WHERE NOT EXISTS (
SELECT *
FROM REP_SESSIONS_HISTORY As H
WHERE H.login_id = D.login_id
)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply