Oracle Trigger Before Delete Raise Error
Contents |
(Русский)ישראל (עברית)المملكة العربية السعودية (العربية)ไทย (ไทย)대한민국 (한국어)中华人民共和国 (中文)台灣 (中文)日本 (日本語) HomeLibraryLearnDownloadsTroubleshootingCommunityForums Ask a question Quick access Forums home Browse forums users FAQ Search related threads Remove From My Forums Answered by: INSTEAD OF DELETE trigger with RAISERROR SQL Server > Transact-SQL Question
Create Trigger To Prevent Deletion
0 Sign in to vote Hello! Before I explane all the issue oracle raise_application_error - I wanna try to present the problem just in few words: In INSTEAD OF DELETE trigger I exception handling in oracle stored procedure example should make INSERT and then RAISERROR. The problem is that if I do RAISERROR then my INSERT never works. Here is my INSTEAD OF DELETE trigger: CREATE TRIGGER
Set Serveroutput On
INSTEAD OF DELETE AS DECLARE @P_ID int BEGIN --Check if bla-bla-bla SELECT @P_ID=P_ID FROM P WHERE
Ora-06512
the reason why I use the RAISERROR statement here is because I can't change the WEB APPLICATION which calls the stored procedure. When the user sees the exception of the RAISERROR - it's satisfying and it works good for me! Everybody is happy :). The problem is that I would like to make the insert to the THE_LOG_TABLE doesn't matter if RAISERROR happens or not. But somehow it's blocked or rollbacked RAISERROR when happens... I tried to surround the insert statement or any other block of the trigger by "begin tran/commit"but itdidn't help me... I would like not to declare another stored procedure for Log_writing (I don't know if it helps or not, but I prefer anyway to keep all this code inside the trigger). Any suggestions? Thank you in advance! Edited by Aleksql Monday, September 26, 2011 2:17 PM Sunday, September 25, 2011 11:12 AM Reply | Quote Answers 0 Sign in to vote First, when you post code, use the squiggly button in the web UI to avoid that the UI mangles your code unreadable. If I understand this co
March 2012 Oracle Magazine Online 2016 2015 2014 2013 2012 2011 2010 January 2012 March 2012 May 2012 July 2012 September 2012 November 2012 As Published pl sql In March/April 2012 TECHNOLOGY: PL/SQL Error Management By Steven Feuerstein Part 6 in a series of articles on understanding and using PL/SQL Even if you write absolutely perfect PL/SQL programs, it is possible and even likely that something will go wrong and an error will occur when those programs are run. How your code responds to https://social.msdn.microsoft.com/Forums/sqlserver/en-US/5e28c4f0-3b2f-4d50-b8c0-e31e83b79cf1/instead-of-delete-trigger-with-raiserror?forum=transactsql and deals with that error often spells the difference between a successful application and one that creates all sorts of problems for users as well as developers. This article explores the world of error management in PL/SQL: the different types of exceptions you may encounter; when, why, and how exceptions are raised; how to define your own exceptions; how http://www.oracle.com/technetwork/issue-archive/2012/12-mar/o22plsql-1518275.html you can handle exceptions when they occur; and how you can report information about problems back to your users. Exception Overview There are three categories of exceptions in the world of PL/SQL: internally defined, predefined, and user-defined. An internally defined exception is one that is raised internally by an Oracle Database process; this kind of exception always has an error code but does not have a name unless it is assigned one by PL/SQL or your own code. An example of an internally defined exception is ORA-00060 (deadlock detected while waiting for resource). A predefined exception is an internally defined exception that is assigned a name by PL/SQL. Most predefined exceptions are defined in the STANDARD package (a package provided by Oracle Database that defines many common programming elements of the PL/SQL language) and are among the most commonly encountered exceptions. One example is ORA-00001, which is assigned the name DUP_VAL_ON_INDEX in PL/SQL and is raised when a unique index constraint is violated. A user-defined exception is one you have declared in the declaration sectio
occurred on a table or view. Triggers support system and other data events on DATABASE and SCHEMA. Oracle Database also supports the execution of PL/SQL or Java procedures. This chapter discusses DML triggers, https://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm INSTEAD OF triggers, and system triggers (triggers on DATABASE and SCHEMA). Topics include: Designing Triggers Creating Triggers Coding the Trigger Body Compiling Triggers Modifying Triggers Enabling and Disabling Triggers Viewing Information About Triggers Examples https://www.mssqltips.com/sqlservertip/1851/prevent-accidental-update-or-delete-commands-of-all-rows-in-a-sql-server-table/ of Trigger Applications Responding to System Events through Triggers Designing Triggers Use the following guidelines when designing your triggers: Use triggers to guarantee that when a specific operation is performed, related actions are performed. oracle trigger Do not define triggers that duplicate features already built into Oracle Database. For example, do not define triggers to reject bad data if you can do the same checking through declarative integrity constraints. Limit the size of triggers. If the logic for your trigger requires much more than 60 lines of PL/SQL code, it is better to include most of the code in a stored procedure and call the oracle trigger before procedure from the trigger. Use triggers only for centralized, global operations that should be fired for the triggering statement, regardless of which user or database application issues the statement. Do not create recursive triggers. For example, creating an AFTER UPDATE statement trigger on the Emp_tab table that itself issues an UPDATE statement on Emp_tab, causes the trigger to fire recursively until it has run out of memory. Use triggers on DATABASE judiciously. They are executed for every user every time the event occurs on which the trigger is created. Creating Triggers Triggers are created using the CREATE TRIGGER statement. This statement can be used with any interactive tool, such as SQL*Plus or Enterprise Manager. When using an interactive tool, a single slash (/) on the last line is necessary to activate the CREATE TRIGGER statement. The following statement creates a trigger for the Emp_tab table. CREATE OR REPLACE TRIGGER Print_salary_changes BEFORE DELETE OR INSERT OR UPDATE ON Emp_tab FOR EACH ROW WHEN (new.Empno > 0) DECLARE sal_diff number; BEGIN sal_diff := :new.sal - :old.sal; dbms_output.put('Old salary: ' || :old.sal); dbms_output.put(' New salary: ' || :new.sal); dbms_output.put_line(' Difference ' || sal_diff); END; / The trigger is fired when DML operations (INSERT, UPDATE, and DELETE statements
Read Comments (13) | Related Tips: More > Triggers ProblemPerforming DML operations through tested application are always safe and efficient. This process also prevents an accidental update or delete of all rows in a table. In my environment, I have a couple of tables that often require ad-hoc updates of certain values or delete of certain rows. On our development SQL Servers ad-hoc DML operations for various purposes are frequent. Unfortunately, there have been a couple of incidents in when someone accidentally issued a DML command without a WHERE clause, hence affecting the entire table. Is there an efficient way to prevent accidental DML operations without a WHERE clause? SolutionIt is a good idea to prevent accidental DML operations which may affect an entire table. For both accidental UPDATE or DELETE prevention, we make use of triggers in this tip. A new trigger may be created and if a trigger is already defined on the table then it may be altered to embed prevention logic. As an example, let's use the Purchasing.VendorContact table in the AdventureWorks database for this hands on example. Here are the basic steps we are going to follow: First, we will create a prevention trigger for UPDATE operations Second, we will create a prevention trigger for DELETE operations Third, we will verify the prevention functionality for both UPDATE and DELETE triggers One final note before we get started, it is assumed that a cluster index exists for the table on which these triggers would be implemented. Create trigger to prevent accidental update of all rows in a table Currently no trigger is defined on the Purchasing.VendorContact table. First we will create an UPDATE trigger to prevent any accidental update of all rows in table. Script # 1: Create UPDATE trigger for SQL Server 2005 and SQL Server 2008 USE AdventureWorks GOCREATE TRIGGER [Purchasing].[uPreventWholeUpdate] ON [Purchasing].[VendorContact] FOR UPDATE AS BEGIN DECLARE @Count int SET @Count = @@ROWCOUNT; IF @Count >= (SELECT SUM(row_count) FROM sys.dm_db_partition_stats WHERE OBJECT_ID = OBJECT_ID('Purchasing.VendorContact' ) AND index_id = 1) BEGIN RAISERROR('Cannot update all rows',16,1) ROLLBACK TRANSACTION RETURN; END END GO We have used the sys.dm_db_partition_stats DMV to get find out the number of rows in the table. Since the DMVs where introduced with SQL Server 2005, the following script can be used: Script # 2: Create UPDATE trigger for SQL Server 2000 USE AdventureWorks GOCREATE TRIGGER [Purchasing].[uPreventWholeUpdate] ON [Purchasing].[VendorContact] FOR UPDATE AS BEGIN DECLARE @Count int SET @Count = @@ROWCOUNT; IF @Count >= (SELECT rowcnt FROM sysindexes WHERE ID =