Re: [SQL] Trigger function to know which fields are being updated

[Bernard Cheung]

Thank you, but my intension is to check whether the user supplies NAME when 
updating the record.

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

[SQL] Trigger function to know which fields are being updated

[Bernard Cheung]

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;
Are there any functions like the Oracle's UPDATING() predicate?

Bernard Cheung

_
Linguaphone :  Learning English? Get Japanese lessons for FREE 
http://go.msnserver.com/HK/46165.asp

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]