I'm using postgresql 7.4.2 and COPY FROM don't trigger INSERT rules.
Definitions:
| CREATE TABLE log.package_sta tus (
| version integer NOT NULL,
| architecture integer NOT NULL,
| distribution integer NOT NULL,
| status_old integer,
| time timestamp NOT NULL,
| UNIQUE (version, architecture, distribution, time)
| );
|
| CREATE TABLE package.status (
| version integer NOT NULL,
| architecture integer NOT NULL,
| distribution integer NOT NULL,
| status integer,
| UNIQUE (version, architecture, distribution)
| );
|
| ALTER TABLE ONLY package.status
| ADD CONSTRAINT version FOREIGN KEY (version) REFERENCES package.version
| ON DELETE CASCADE;
|
| ALTER TABLE ONLY package.status
| ADD CONSTRAINT architecture FOREIGN KEY (architecture) REFERENCES def..architectu re;
|
| ALTER TABLE ONLY package.status
| ADD CONSTRAINT distribution FOREIGN KEY (distribution) REFERENCES def..distributi on;
|
| ALTER TABLE ONLY package.status
| ADD CONSTRAINT status FOREIGN KEY (status) REFERENCES def.package_sta tus;
|
| CREATE INDEX architecture_di stribution_inde x ON package.status (architecture, distribution);
|
| CREATE RULE status_insert AS ON INSERT TO package.status
| DO INSERT INTO log.package_sta tus (version, architecture, distribution, time)
| VALUES (NEW.version, NEW.architectur e, NEW.distributio n, current_timesta mp);
|
| CREATE RULE status_update AS ON UPDATE TO package.status
| WHERE NEW.status <> OLD.status
| DO INSERT INTO log.package_sta tus (version, architecture, distribution, status_old, time)
| VALUES (NEW.version, NEW.architectur e, NEW.distributio n, OLD.status, current_timesta mp);
The data is inserted via the following call into an mostly empty database:
| COPY package.status (version, architecture, distribution, status) FROM STDIN
It was created from a template which contains a few functions and
operators.
After all data is commited I get the following, it is reproducable:
| multibuild=> SELECT count(*) from package.status;
| count
| -------
| 15130
| (1 row)
|
| multibuild=> SELECT count(*) from log.package_sta tus;
| count
| -------
| 0
| (1 row)
Tests with INSERT always triggers the rule.
Bastian
--
.... The prejudices people feel about each other disappear when they get
to know each other.
-- Kirk, "Elaan of Troyius", stardate 4372.5
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)
iEYEARECAAYFAkC 57MYACgkQnw66O/MvCNFoowCgjaowH 5YWuNlNt/vc5bUdOHaO
VVkAoICGkZgbJIj drwPwbxyYXnILE0 Zd
=EFTg
-----END PGP SIGNATURE----- 1 1923
On 5/30/2004 10:16 AM, Bastian Blank wrote: I'm using postgresql 7.4.2 and COPY FROM don't trigger INSERT rules.
Right, and it never did. COPY does not pass the query rewrite engine.
Jan Definitions: | CREATE TABLE log.package_sta tus ( | version integer NOT NULL, | architecture integer NOT NULL, | distribution integer NOT NULL, | status_old integer, | time timestamp NOT NULL, | UNIQUE (version, architecture, distribution, time) | ); | | CREATE TABLE package.status ( | version integer NOT NULL, | architecture integer NOT NULL, | distribution integer NOT NULL, | status integer, | UNIQUE (version, architecture, distribution) | ); | | ALTER TABLE ONLY package.status | ADD CONSTRAINT version FOREIGN KEY (version) REFERENCES package.version | ON DELETE CASCADE; | | ALTER TABLE ONLY package.status | ADD CONSTRAINT architecture FOREIGN KEY (architecture) REFERENCES def.architectur e; | | ALTER TABLE ONLY package.status | ADD CONSTRAINT distribution FOREIGN KEY (distribution) REFERENCES def.distributio n; | | ALTER TABLE ONLY package.status | ADD CONSTRAINT status FOREIGN KEY (status) REFERENCES def.package_sta tus; | | CREATE INDEX architecture_di stribution_inde x ON package.status (architecture, distribution); | | CREATE RULE status_insert AS ON INSERT TO package.status | DO INSERT INTO log.package_sta tus (version, architecture, distribution, time) | VALUES (NEW.version, NEW.architectur e, NEW.distributio n, current_timesta mp); | | CREATE RULE status_update AS ON UPDATE TO package.status | WHERE NEW.status <> OLD.status | DO INSERT INTO log.package_sta tus (version, architecture, distribution, status_old, time) | VALUES (NEW.version, NEW.architectur e, NEW.distributio n, OLD.status, current_timesta mp);
The data is inserted via the following call into an mostly empty database: | COPY package.status (version, architecture, distribution, status) FROM STDIN
It was created from a template which contains a few functions and operators.
After all data is commited I get the following, it is reproducable: | multibuild=> SELECT count(*) from package.status; | count | ------- | 15130 | (1 row) | | multibuild=> SELECT count(*) from log.package_sta tus; | count | ------- | 0 | (1 row)
Tests with INSERT always triggers the rule.
Bastian
--
#============== =============== =============== =============== ===========#
# 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 1: subscribe and unsubscribe commands go to ma*******@postg resql.org This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Dave C. |
last post by:
Hello,
I have created the following trigger:
CREATE TRIGGER tr_UDFTest ON UserDefinedFields FOR INSERT, UPDATE
AS
DECLARE @foobar varchar(100)
SELECT @foobar= foobar FROM inserted
IF ( @foobar = 'foobar') INSERT INTO LogTable (LogText) values ('Found foobar')
|
by: Terri |
last post by:
I'm using xp_cmdshell to output a text file from a trigger like this
CREATE TRIGGER ON tblApplications
FOR INSERT
AS
DECLARE @FirstName varchar(75)
DECLARE @LastName varchar(75)
Declare @strcmdshell varchar(150)
|
by: Jason |
last post by:
I have a table that matches up Securities and Exchanges. Individual
securities can belong on multiple exchanges. One of the columns, named
PrimaryExchangeFlag, indicates if a particular exchange is the primary
exchange for that symbol. Each symbol can only have one primary
exchange.
I am trying to write a insert/update/delete trigger that enforces this
rule. The rules I have thought of are as follows:
Insert If new row has flag...
|
by: Edward Diener |
last post by:
Coming from the C++ world I can not understand the reason why copy
constructors are not used in the .NET framework. A copy constructor creates
an object from a copy of another object of the same kind. It sounds simple
but evidently .NET has difficulty with this concept for some reason. I do
understand that .NET objects are created on the GC heap but that doesn't
mean that they couldn't be copied from another object of the same kind when...
|
by: rkrueger |
last post by:
Given the following 3 Tables:
CREATE TABLE (
NOT NULL ,
NOT NULL CONSTRAINT
DEFAULT (getdate()),
NULL ,
CONSTRAINT PRIMARY KEY CLUSTERED
(
| |
by: Ericson Smith |
last post by:
Hi,
Is there any way to prevent a trigger from firing during a COPY operation?
We have a case where we dump the records from a table, truncate it, and
copy the records back in. However, there is a trigger on that table,
which will insert a record in a logging table. Is there a way to prevent
this trigger from firing during the COPY FROM process?
Just a little more background, I tried to make the function a little
|
by: Dave Sisk |
last post by:
Hey folks:
I'm trying to do this:
CREATE TRIGGER datawhse.emp_ti
AFTER INSERT ON emp
REFERENCING NEW AS n
FOR EACH ROW MODE DB2ROW
BEGIN
DECLARE v_rrn DECIMAL(15,0);
|
by: Dima Gofman |
last post by:
I have a trigger on UPDATE on a table. I'm running some maintenance
UPDATE and DELETE queries which I want the trigger to ignore but at the
same time I want other UPDATE queries that other users might be running
to keep triggering the trigger. Is there a SET statement perhaps that
I could set before my query? Or a clause in the UPDATE statement?
This is on MSSQL 2000 server, on Win2k3
|
by: gshawn3 |
last post by:
Hi,
I am having a hard time creating a Trigger to update an Oracle
database. I am using a SQL Server 2005 Express database on a Win XP Pro
SP2 desktop, linked to an Oracle 10g database on a remote Windows 2003
server. Both machines are on the same domain and very close physically
(<1ms ping).
I have set up the Oracle linked server in SQLEXPRESS, added the
login/pw information, and I can execute select and update queries
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
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: 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...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
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: 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
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |