Hi,
I am using postgres 7.4.5 on Redhat Enterprise Linux 3.
My background is really on Oracle, and I am porting a largish database
over to postgres.
Here is my problem:
On oracle, I had a table with an "on update or delete" trigger that
copied the current row out to an audit table. Works like a champ. On
postgres, when I try to delete a row, all it gives back to me is "DELETE
0" and does nothing.
Here is the text of the trigger:
~~~~~~~~~~
CREATE OR REPLACE FUNCTION public.func_job _status_upd()
RETURNS trigger AS
'
begin
insert into x_job_status values ( OLD.job_id, OLD.job_status_ type_id,
OLD.status_date , OLD.notes, OLD.edit_person _id, OLD.edit_date);
return new;
end;
'
LANGUAGE 'plpgsql' VOLATILE;
~~~~~~~~~~
Any help would be appreciated!
Thanks,
naeem 4 9954
On 10/25/2004 2:56 PM, Naeem Bari wrote: Hi,
I am using postgres 7.4.5 on Redhat Enterprise Linux 3.
My background is really on Oracle, and I am porting a largish database over to postgres.
Here is my problem:
On oracle, I had a table with an "on update or delete" trigger that copied the current row out to an audit table. Works like a champ. On postgres, when I try to delete a row, all it gives back to me is "DELETE 0" and does nothing.
Here is the text of the trigger:
~~~~~~~~~~ CREATE OR REPLACE FUNCTION public.func_job _status_upd() RETURNS trigger AS ' begin insert into x_job_status values ( OLD.job_id, OLD.job_status_ type_id, OLD.status_date , OLD.notes, OLD.edit_person _id, OLD.edit_date); return new;
There is no NEW row on DELETE. You can either let the trigger fire
AFTER, causing its return value to be ignored, or define different
trigger procedures for UPDATE/DELETE, or you can check inside the
trigger for which event it was actually fired and return NEW/OLD
accordingly.
Jan
end; ' LANGUAGE 'plpgsql' VOLATILE; ~~~~~~~~~~
Any help would be appreciated!
Thanks, naeem
--
#============== =============== =============== =============== ===========#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#============== =============== =============== ====== Ja******@Yahoo. com #
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
"Naeem Bari" <na********@agi lissystems.com> writes: CREATE OR REPLACE FUNCTION public.func_job _status_upd() RETURNS trigger AS ' begin insert into x_job_status values ( OLD.job_id, OLD.job_status_ type_id, OLD.status_date , OLD.notes, OLD.edit_person _id, OLD.edit_date); return new; end; ' LANGUAGE 'plpgsql' VOLATILE;
If this is a BEFORE trigger, you probably need "RETURN OLD". "NEW" will
be NULL in a delete trigger, so you're returning NULL which cancels the
operation.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)
On 10/25/2004 3:33 PM, Franco Bruno Borghesi wrote: I've made a test case, and setting the trigger BEFORE DELETE doesn't delete the rows from the table (but it does execute the trigger, and it does insert the rows in the audit table), I dont' know why :(.
Because the internal variable for NEW is initialize to NULL and
returning NULL from a BEFORE trigger silently suppresses the operation
on the original row that it was fired for.
Jan Anyway, setting the trigger AFTER DELETE works ok.
On Mon, 2004-10-25 at 15:56, Naeem Bari wrote:
Hi, I am using postgres 7.4.5 on Redhat Enterprise Linux 3. My background is really on Oracle, and I am porting a largish database over to postgres. Here is my problem: On oracle, I had a table with an ´on update or delete¡ trigger that copied the current row out to an audit table. Works like a champ. On postgres, when I try to delete a row, all it gives back to me is ´DELETE 0¡ and does nothing. Here is the text of the trigger: ~~~~~~~~~~
CREATE OR REPLACE FUNCTION public.func_job _status_upd()
RETURNS trigger AS
'
begin
insert into x_job_status values ( OLD.job_id, OLD.job_status_ type_id, OLD.status_date , OLD.notes, OLD.edit_person _id, OLD.edit_date);
return new;
end;
'
LANGUAGE 'plpgsql' VOLATILE;
~~~~~~~~~~ Any help would be appreciated! Thanks,
naeem
--
#============== =============== =============== =============== ===========#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#============== =============== =============== ====== Ja******@Yahoo. com #
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
On 10/25/2004 3:47 PM, Tom Lane wrote: "Naeem Bari" <na********@agi lissystems.com> writes: CREATE OR REPLACE FUNCTION public.func_job _status_upd() RETURNS trigger AS ' begin insert into x_job_status values ( OLD.job_id, OLD.job_status_ type_id, OLD.status_date , OLD.notes, OLD.edit_person _id, OLD.edit_date); return new; end; ' LANGUAGE 'plpgsql' VOLATILE; If this is a BEFORE trigger, you probably need "RETURN OLD". "NEW" will be NULL in a delete trigger, so you're returning NULL which cancels the operation.
.... which would then again not work for the UPDATE case (not with the
same internal consequences though).
Jan regards, tom lane
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)
--
#============== =============== =============== =============== ===========#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#============== =============== =============== ====== Ja******@Yahoo. com #
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Ryan |
last post by:
I have a table in my database on SQL Server which holds a file name
that refers to a file that is stored on the server. I would like to
create a trigger to delete this file from the server if the row in the
table is deleted. I have been trying to use this command in a trigger
(<filename> is the name and path of the file):
xp_cmdshell "delete <filename>"
If some one could please help I would appreciate it very much. I
would love a...
|
by: Bob Ganger |
last post by:
Hello,
I am working on a project using SQL Server 2000 with a database containing
about 10 related tables with a lot of columns containing text. The total
current size of the database is about 2 Gig. When I delete data from the
database, it takes a lot of system resources and monopolizes the database so
that all other query requests are slow as mud!
Ideally, I would like to be able to issue delete commands to the database on
a...
|
by: Aidan Whitehall |
last post by:
Have gone through BOL and Google, but can't find the answer... please help
with a simple Q. I'm trying to create a simple cascade delete trigger in SQL
Server 7 where deleting "parent" records in table X delete corresponding
child records in table Y.
Table X
=========
X_ID
SOME_VAL
|
by: uninfmx |
last post by:
Hi
If one or mode records get deleted from t1 (see below), I'd like
delete all the corresponding records from t2. There is no foreign key
relationship between t2 and t1, so cascading delete is not an option.
V8.2 AIX
create table t1
(
x int
|
by: John Rivers |
last post by:
Hello,
I think this will apply to alot of web applications:
users want the ability to delete a record in table x
this record is related to records in other tables
and those to others in other tables etc.
in other words we must use cascade delete to do
| |
by: R.Welz |
last post by:
Hello.
I want to discuss a problem I have with my database design becourse I
feel I cannot decide wheather I am on the right way of doing things.
First of all, I am writing a literature and magazine database with web
(PHP) and C++ Interface, serving over the web and in a very fast LAN.
So my concern is about performance (and aestaetic by doing the things
as optimal as possible.) This is my first database at all, but I have
read a lot of...
|
by: jan.marien |
last post by:
we have a table with jobs and a table with job_history information.
Users can define jobs and let them run every X minutes/hours , like a
cronjob.
The jobs table has the following trigger:
CREATE TRIGGER JOBS_AFTER_DELETE
AFTER DELETE ON JOBS
REFERENCING OLD AS o
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
|
by: Alexis |
last post by:
Hello,
I'm facing oracle trigger problem. Anyone can help or advise how to resolve it?
Below are the explaination on my problem
I've created a trigger for my program. When there is a new data insert into table moto_pvs_pulse_daily_yield_tmp, my program will checking and copy the data by each row and insert into table mt_pulse_daily_yield_tmp which is in other instance.
After I success insert into table mt_pulse_daily_yield_tmp, I...
|
by: Ed Dror |
last post by:
Hi there,
I'm using ASP.NET 2.0 and SQL Server 2005 with VS 2005 Pro.
I have a Price page (my website require login) with GridView with the
following columns
PriceID, Amount, Approved, CrtdUser and Date
And Edit and Delete buttons
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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...
| |
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,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
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...
| |