Trigger Question - PostgreSQL Database

Terry Lee Tucker

Hi,

I have a simple question. I have been looking at the HTML docs for Postgres
version: PostgreSQL 7.2.3-RH on i686-pc-linux-gnu, compiled by GCC 2.96. I
need to know if it is necessary to RETURN NULL from a trigger function after
raising an exception with RAISE EXCEPTION. We are doing many different data
validations in a "BEFORE UPDATE OR INSERT" trigger and we have been raising
an exception with an error string such that the error comes back to the
X-Windows applicaton. I read that returning NULL prevents other triggers from
fireing but doesn't RAISE EXCEPTION abort the tranaction and cause all
processing to stop?

Thanks in advance for your help...
--
"The world we have created is a product of our thinking. It cannot be
changed without changing our thinking."

--Albert Einstein

Work: 1-336-372-6812
Cell: 1-336-363-4719
email: te***@esc1.com

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 22 '05 #1

Subscribe Reply

2849

ezra epstein

Raising an exception means the return statement is never reached, so never
executed, so not relevant. Just like exceptions in other languages.

Yes, an exception will abort the transaction and stop further processing.

You may still want to conditionally return NULL instead of NEW (or OLD) on
some trigger-invoked SPs, but that has nothing to do with exceptions (the 2
are mutually exclusive).

== Ezra Epstein
"Terry Lee Tucker" <te***@esc1.com > wrote in message
news:2004011117 13.54786.te***@ esc1.com...
Hi,

I have a simple question. I have been looking at the HTML docs for Postgres
version: PostgreSQL 7.2.3-RH on i686-pc-linux-gnu, compiled by GCC 2.96. I
need to know if it is necessary to RETURN NULL from a trigger function after
raising an exception with RAISE EXCEPTION. We are doing many different data
validations in a "BEFORE UPDATE OR INSERT" trigger and we have been raising
an exception with an error string such that the error comes back to the
X-Windows applicaton. I read that returning NULL prevents other triggers
from
fireing but doesn't RAISE EXCEPTION abort the tranaction and cause all
processing to stop?

Thanks in advance for your help...
--
"The world we have created is a product of our thinking. It cannot be
changed without changing our thinking."

--Albert Einstein

Work: 1-336-372-6812
Cell: 1-336-363-4719
email: te***@esc1.com

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 22 '05 #2

Similar topics

8095

Using xp_cmdshell in INSERT trigger: bad idea?

by: Joel Thornton | last post by:

Whenever something is inserted to a given table, I want to run some shell commands using xp_cmdshell. Would it be a bad idea to put this xp_cmdshell in the INSERT trigger of this table? I understand that when using xp_cmdshell, the sql thread in question waits until xp_cmdshell finishes what it's doing. Does this mean if my xp_cmdshell call takes 30 seconds, that nobody else can insert to this table until my xp_cmdshell and rest of the...

Microsoft SQL Server

2004

SP waits for a delete trigger?

by: Matik | last post by:

Hello to all, I have a small question. I call the SP outer the DB. The procedure deletes some record in table T1. The table T1 has a trigger after delete. This is very importand for me, that the SP will be finished ASAP, that's why, I do not want, and I do not need to wait for a trigger.

Microsoft SQL Server

6557

Trigger performance

by: Scott CM | last post by:

I have a multi-part question regarding trigger performance. First of all, is there performance gain from issuing the following within a trigger: SELECT PrimaryKeyColumn FROM INSERTED opposed to: SELECT * FROM INSERTED

Microsoft SQL Server

2743

trigger vs. application code performance

by: robert | last post by:

typed this into the ibm.com search window, but didn't get anything that looked like it would answer a question: +trigger +faster +db2 +cobol the question: are DB2 (390/v6, at the moment) triggers better or worse (and how much so, of course) than the same functionality coded in COBOL? assuming, of course, equal competence in the code.

DB2 Database

7148

V5R2 DB2/UDB iSeries: can a system trigger call a SQL stored proc?

by: Dave Sisk | last post by:

I've created a system or external trigger on an AS/400 file a.k.a DB2 table. (Note this is an external trigger defined with the ADDPFTRG CL command, not a SQL trigger defined with the CREATE TRIGGER statement.) I've also defined a SQL stored proc, and the trigger is set to call this SP. I've posted the simplified source below. I can manually call the stored proc, and the external trigger is created without any errors. However, when I do...

DB2 Database

3298

Trigger structure

by: Bob Stearns | last post by:

I have two (actually many) dates in a table I want to validate on insertion. The following works in the case of only one WHEN clause but fails with two (or more), with the (improper? inappropriate?) error message: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: CREATE TRIGGER IS3.date_later_001i NO C;BEGIN-OF-STATEMENT;<space> which is interpreted as: An unexpected token "CREATE TRIGGER IS3.date_later_001i NO C" was found

DB2 Database

4953

Advanced - C# SQL Trigger Question

by: ChrisN | last post by:

Hello all, I have a quick question. I'm using a C# object to commit new rows to a database. In the database I have an INSERT Trigger watching values come in. If the record to be committed fails the trigger's test, the trigger rolls back the INSERT command and no changes are made to the database. As far as my object is concerned, the transaction went through either way (no matter what the trigger did). What I need is for the object...

C# / C Sharp

3731

Help with INSERT TRIGGER - fails with error.

by: teddysnips | last post by:

I need a trigger (well, I don't *need* one, but it would be optimal!) but I can't get it to work because it references ntext fields. Is there any alternative? I could write it in laborious code in the application, but I'd rather not! DDL for table and trigger below. TIA

Microsoft SQL Server

9312

One trigger, many tables - how to automate?

by: Ots | last post by:

I'm using SQL 2000, which is integrated with a VB.NET 2003 app. I have an Audit trigger that logs changes to tables. I want to apply this trigger to many different tables. It's the same trigger, with the exception of the table name. I could manually change the table name in the trigger and create it, over and over, but I'd like to automate this - by iterating through the collection of tables and passing the tablename to something that...

Microsoft SQL Server

3574

Row and Statement based Trigger Question

by: JohnO | last post by:

Hi All, This question is related to iSeries V5R4 and db2. I want to implement an AFTER DELETE trigger to save the deleted rows to an archive table, I initially defined it as a FOR EACH STATEMENT trigger that would insert all the deleted rows in one operation like this: CREATE TRIGGER MyTable_TD

DB2 Database

10350

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

10157

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

10097

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

7505

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

5386

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

5518

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

4055

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

3658

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

2887

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General