September 11, 2008 at 8:42 am
Hi,
The following trigger was generated from oracle trigger using SSMA Tool a migration tool for oracle to SQL server. but this trigger should stop a row from being deleted whenever you use a delete operation . but rows are getting deleted even though the row is locked. its a tool generated trigger it should work fine right.
Plz help.
cREATE TRIGGER dbo.InsteadOfDeleteOn$ISSUE_MONTH_FACT
ON dbo.ISSUE_MONTH_FACT
INSTEAD OF DELETE
AS
/* This trigger raises application error when row is locked and there is delete operation
* Generated by SQL Server Migration Assistant for Oracle.
* Contact ora2sql@microsoft.com or visit http://www.microsoft.com/sql/migration for more information.
*/
BEGIN
SET NOCOUNT ON
/* column variables declaration*/
DECLARE
@old$0 uniqueidentifier,
@old$LOCKED numeric(1, 0)
DECLARE
ForEachDeletedRowTriggerCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT ROWID, LOCKED
FROM deleted
OPEN ForEachDeletedRowTriggerCursor
FETCH ForEachDeletedRowTriggerCursor
INTO @old$0, @old$LOCKED
WHILE @@fetch_status = 0
BEGIN
/* row-level triggers implementation: begin*/
BEGIN
/* ERwin Builtin Fri Feb 27 10:35:36 2004 default body for DEL_ISS_MONTH_FACT*/
BEGIN
DECLARE
@numrows int
IF @old$LOCKED = 1
BEGIN
DECLARE
@db_raise_application_error_message nvarchar(4000)
SET @db_raise_application_error_message = N'ORA' + CAST(-20101 AS nvarchar) + N': ' + N'Row is locked'
RAISERROR(59998, 16, 1, @db_raise_application_error_message)
END
END
END
/* row-level triggers implementation: end*/
/* DML-operation emulation*/
DELETE dbo.ISSUE_MONTH_FACT
WHERE ROWID = @old$0
FETCH ForEachDeletedRowTriggerCursor
INTO @old$0, @old$LOCKED
END
CLOSE ForEachDeletedRowTriggerCursor
DEALLOCATE ForEachDeletedRowTriggerCursor
END
GO
September 11, 2008 at 8:57 am
This looks like it will prevent deleting ONLY if there is a value of "1" in the Locked field. The row isn't necessarily locked.
also, this is an INSTEAD of trigger, so it works in a different way. Here's an article to help you learn about this:
http://msdn.microsoft.com/en-us/library/aa224818(SQL.80).aspx
September 11, 2008 at 9:34 am
It's also going to run less than optimally, seeing as there's a cursor in it.
What's supposed to happen?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply