Trigger - SQL Server Forums

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

Trigger

Author

Topic

khasim76
Starting Member

35 Posts

Posted - 2008-09-11 : 10:34:50

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

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-11 : 10:57:38

>>its a tool generated trigger it should work fine right.
Ever hear the expression, "That guy is a real tool".

I think you will either need this after the RAISERROR:
rollback
return

or you need to set this at the top:
set xact_abort on

EDIT:
If you do the rollback/return then you need to make sure you're close and dealocated your curusor variable.

EDIT (again):
Since this is an INSTEAD of trigger you don't need a ROLLBACK you just need to NOT do the delete statement. Actually sql server recomends that explicit ROLLBACKs should not be used any more within trigger code.

Be One with the Optimizer
TG

khasim76
Starting Member

35 Posts

Posted - 2008-09-11 : 11:13:49

thank you very much it works i really appreciate your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources