Re: Trigger function to know which fields are being updated
От | Riccardo Facchini |
---|---|
Тема | Re: Trigger function to know which fields are being updated |
Дата | |
Msg-id | 20040511074622.64446.qmail@web13902.mail.yahoo.com обсуждение исходный текст |
Ответ на | Trigger function to know which fields are being updated ("Bernard Cheung" <cheungsw@hotmail.com>) |
Список | pgsql-sql |
> --- Bernard Cheung <cheungsw@hotmail.com> wrote: > > 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 <jeff_eckermann@yahoo.com> > > >To: Bernard Cheung <cheungsw@hotmail.com>, > pgsql-sql@postgresql.org > > >Subject: Re: [SQL] Trigger function to know which fields are being > > updated > > >Date: Mon, 10 May 2004 14:38:56 -0700 (PDT) > > > > > > > > >--- Bernard Cheung <cheungsw@hotmail.com> 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 > > >majordomo@postgresql.org > > > > _________________________________________________________________ > > 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 > One way that comes to my mind is to check if is there any differencebetween the new.name and old.name...something like:if(old.name <> new.name) then -- something happening here...else -- nothing happened, or the name has not been changed.endif;regards, ===== Riccardo G. Facchini
В списке pgsql-sql по дате отправления:
Предыдущее
От: Jeff EckermannДата:
Сообщение: Re: Trigger function to know which fields are being updated