For example the trigger shall allow statement 1 and block statement 2:
1. UPDATE COMPANY SET NAME = 'ABC', ADDRESS = '123 Drive' WHERE COMPANY_ID = 1;
2. UPDATE COMPANY SET ADDRESS = '123 Drive' WHERE COMPANY_ID = 1;
I want the trigger to ensure that the user must provide value for NAME when updating this record.
From: Jeff Eckermann <[EMAIL PROTECTED]> To: Bernard Cheung <[EMAIL PROTECTED]>, [EMAIL PROTECTED] Subject: Re: [SQL] Trigger function to know which fields are being updated Date: Mon, 10 May 2004 14:38:56 -0700 (PDT)
--- Bernard Cheung <[EMAIL PROTECTED]> wrote: > I am writing a trigger function. How can I know > which fields are being > updated in the PL/SQL function? > > For example I have a table here: > > > CREATE TABLE COMPANY ( > COMPANY_ID VARCHAR(10) NOT NULL, > NAME VARCHAR(30), > ADDRESS VARCHAR(30)); > > I want to write a trigger to block all update > statements without updating > NAME. I tried the following code block and it > doesn't work: > > > IF TG_OP = ''UPDATE'' THEN > IF NEW.NAME IS NULL THEN > RAISE NOTICE ''Field NAME must be > provided!''; > END IF; > END IF;
That should work. Perhaps "name" is not actually null, but rather an empty string? In that case, your test needs to be: "IF NEW.NAME IS NULL OR NEW.NAME = '''' THEN..."
> > Are there any functions like the Oracle's UPDATING() > predicate? > > Bernard Cheung > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
_________________________________________________________________
Linguaphone : Learning English? Get Japanese lessons for FREE http://go.msnserver.com/HK/46165.asp
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
