db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@Sourcery.Org>
Subject Re: Use of If control statement in row level trigger
Date Fri, 12 Aug 2005 16:25:06 GMT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Shouldn't you be using <b>oldRow </b>and <b>newRow </b>instead of
old and new in the CASE statement? Like:<br>
<font size="2"><strong><br>
&nbsp;&nbsp;&nbsp; CASE WHEN newRow.ROLLNO!=oldRow.ROWNO THEN 'Y' ELSE null END</strong></font><br>
<br>
Satheesh<br>
<br>
sube singh wrote:
<blockquote cite="mid74130ca105081201596cf49aa2@mail.gmail.com"
 type="cite">
  <div>Hello,</div>
  <div>&nbsp;</div>
  <div>Thanks for prompt reply. I have tried following query </div>
  <div>&nbsp;</div>
  <div><font size="2">
  <p><strong>Create trigger APP.TRU_A_STUDENT after update on
APP.STUDENT Referencing old as oldRow new as newRow For each Row MODE
DB2SQL Insert Into APP.REP_SHADOW_STUDENT ( Rep_common_id,
Rep_operationType, Rep_status, ROLLNO , NAME , PAY , CLS ,
rep_old_ROLLNO , Rep_server_name , Rep_PK_Changed ) Values ((Select
max(Rep_cid) from Rep_LogTable) , 'U' , 'A' , newRow.ROLLNO ,
newRow.NAME , newRow.PAY , newRow.CLS , oldRow.ROLLNO ,
'sube_3001',(CASE WHEN new.ROLLNO!=old.ROWNO THEN 'Y' ELSE null END))</strong></p>
  <p>It give the following error message : </p>
  <font color="#ff0000" size="2">
  <p>ERROR 42X04: Column 'NEW.ROLLNO' is not in any table in the FROM
list or it appears within a join specification and is outside the scope
of the join specification or it appears in a HAVING clause and is not
in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then
'
NEW.ROLLNO' is not a column in the target table.</p>
  </font>
  <p>&nbsp;</p>
  <p><font color="#000000">Thanks and Regards,</font></p>
  <p>Sube Singh</p>
  <p>&nbsp;</p>
  <p><strong></strong>&nbsp;</p>
  </font></div>
  <div><br>
  <br>
&nbsp;</div>
  <div><span class="gmail_quote">On 8/12/05, <b
 class="gmail_sendername">Ali Demir</b> &lt;<a
 href="mailto:demir4@yahoo.com">demir4@yahoo.com</a>&gt; wrote:</span>
  <blockquote class="gmail_quote"
 style="border-left: 1px solid rgb(204, 204, 204); margin: 0px 0px 0px 0.8ex; padding-left:
1ex;">There
is no such thing as declare variable. Instead you can use something
like this instead of pkchanged inside your insert statement:
    <br>
    <br>
(CASE WHEN new.ROLLNO&lt;&gt;old.ROWNO THEN 'Y' ELSE 'N' END)<br>
    <br>
Try this to get an idea about how it works:<br>
    <br>
SELECT (CASE WHEN 1&lt;&gt;0 THEN 'Y' ELSE 'N' END)&nbsp; as COL1 FROM
(VALUES(1)) as t<br>
    <br>
Also, there is no begin-end around trigger body. You need to have
single statement inside one trigger. You can have multiple triggers if
you need multiple statements.
    <br>
    <br>
Regards,<br>
Suavi
    <div><span class="e" id="q_105a99864f7f3774_1"><br>
    <br>
    <br>
At 12:08 AM 8/12/2005, you wrote:<br>
    <blockquote type="cite">Hi,<br>
      <br>
I would like to insert the value of pk_changed into the table <br>
student if old and new primary key value is not same. Plz see the
following statement<br>
      <br>
Create trigger APP.TRU_A_STUDENT
      <br>
after update on APP.STUDENT<br>
Referencing old as oldRow new as newRow For each Row MODE DB2SQL<br>
      <b>delclare pkchanged char(1);<br>
begin<br>
if(newRow.ROLLNO!=oldRow.ROLLNO!= ) then<br>
pkchanged ='Y' ;<br>
end if;<br>
end;</b><br>
      <b><br>
      </b>Insert Into APP.SHADOW_STUDENT(common_id, operationType,
status,<br>
ROLLNO , NAME , PAY , CLS , old_ROLLNO , server_name ) Values ((Select<br>
max(cid) from&nbsp; TempTable,pk_change_cols) , 'U' , 'A' , newRow.ROLLNO ,
newRow.NAME ,<br>
newRow.PAY , newRow.CLS , oldRow.ROLLNO , 'sube_3001',<b>pkchanged</b> )<br>
      <br>
I hope that you will reply me as soon as possible <br>
&nbsp;<br>
Thanks ans Regards,<br>
&nbsp;<br>
Sube Singh<br>
      <br>
      <br>
      <br>
      <br>
On 8/12/05, Ali Demir &lt;
      <a onclick="return top.js.OpenExtLink(window,event,this)"
 href="mailto:demir4@yahoo.com" target="_blank">demir4@yahoo.com</a>&gt;
wrote:<br>
&gt; Where do you use the pkchanged that you are setting to 'Y'? In
some cases,<br>
&gt; you can use CASE statement inside INSERT etc too if you want. I am
assuming <br>
&gt; you are trying to execute the triggered insert ONLY IF the Primary
Key value<br>
&gt; of the new row is different than the old row and the PK column is
ROLLNO.
      <br>
&gt; <br>
&gt; In general, it could be like this (@see bold): <br>
&gt; <br>
&gt; Create trigger APP.TRU_A_STUDENT <br>
&gt; after update on APP.STUDENT <br>
&gt; Referencing old as oldRow new as newRow For each Row MODE DB2SQL <br>
&gt; Insert Into APP.SHADOW_STUDENT(common_id, operationType, status, <br>
&gt; ROLLNO , NAME , PAY , CLS , old_ROLLNO , server_name ) SELECT *
FROM (Values<br>
&gt; ((Select<br>
&gt; max(cid) from&nbsp; TempTable) , 'U' , 'A' , newRow.ROLLNO ,
newRow.NAME ,<br>
&gt; newRow.PAY , newRow.CLS , oldRow.ROLLNO , 'sube_3001')) as t WHERE<br>
&gt; (newRow.ROLLNO&lt;&gt;oldRow.ROLLNO)<br>
&gt; <br>
&gt; I don't have your db schema, so I did not test, but you get the
idea: When
      <br>
&gt; you append the where clause, the select returns nothing, and you
insert <br>
&gt; nothing if PK has not changed.<br>
&gt; <br>
&gt; VALUES syntax when used inside a FROM clause is as follows:<br>
&gt; <br>
&gt; select * from (VALUES(1)) as temp
      <br>
&gt; <br>
&gt; Excuse my html formatting if it causes trouble. <br>
&gt; <br>
&gt; Regards,<br>
&gt; Suavi<br>
&gt; <br>
&gt; <br>
&gt; At 11:27 PM 8/11/2005, you wrote:<br>
&gt; Hi,<br>
&gt; <br>
&gt; Lot of thanks for your reply. I have the following trigger.&nbsp;&nbsp;&nbsp;
<br>
&gt; <br>
&gt; <br>
&gt; Create trigger APP.TRU_A_STUDENT <br>
&gt; after update on APP.STUDENT <br>
&gt; Referencing old as oldRow new as newRow For each Row MODE DB2SQL <br>
&gt; Insert Into APP.SHADOW_STUDENT(common_id, operationType, status,
      <br>
&gt; ROLLNO , NAME , PAY , CLS , old_ROLLNO , server_name ) Values
((Select <br>
&gt; max(cid) from&nbsp; TempTable) , 'U' , 'A' , newRow.ROLLNO ,
newRow.NAME ,<br>
&gt; newRow.PAY , newRow.CLS , oldRow.ROLLNO , 'sube_3001')
      <br>
&gt; <br>
&gt; <br>
&gt; I would like to add the following statement in trigger before <br>
&gt; execution of insert statement :<br>
&gt; <br>
&gt; delclare pkchanged char(1);<br>
&gt; begin <br>
&gt; if(newRow.ROLLNO!=oldRow.ROLLNO!=
) then <br>
&gt; pkchanged ='Y' ;<br>
&gt; end if;<br>
&gt; end;<br>
&gt; <br>
&gt; Please reply me I am waiting. <br>
&gt; <br>
&gt; Thanks and Regrds,<br>
&gt; Sube Singh<br>
&gt; <br>
&gt; <br>
&gt; <br>
&gt; <br>
&gt; On 8/12/05, Ali Demir &lt;
      <a onclick="return top.js.OpenExtLink(window,event,this)"
 href="mailto:demir4@yahoo.com" target="_blank">demir4@yahoo.com</a>&gt;
wrote:<br>
&gt; &gt; Conditionally firing the trigger is not supported yet if i
remember <br>
&gt; correct,<br>
&gt; &gt; but you can carry the IF condition into the where clause.<br>
&gt; &gt; <br>
&gt; &gt; Example:<br>
&gt; &gt; <br>
&gt; &gt; create trigger S.TRIGNAME<br>
&gt; &gt; after update of COL1 on S.T1<br>
&gt; &gt; referencing NEW as N OLD as O <br>
&gt; &gt; for each row mode db2sql&nbsp; <br>
&gt; &gt; insert into S.T2(COLN)&nbsp; <br>
&gt; &gt; select COLX&nbsp; <br>
&gt; &gt; from S.T3 <br>
&gt; &gt; where (not N.COL1=O.COL1);<br>
&gt; &gt; <br>
&gt; &gt; <br>
&gt; &gt; This will insert nothing if the condition (not N.COL1=O.COL1)
evaluates to<br>
&gt; &gt; FALSE. [may need to check for NULLs separately if cols are
nullable in<br>
&gt; these<br>
&gt; &gt; things] <br>
&gt; &gt; <br>
&gt; &gt; It will feel like trigger did not fire.<br>
&gt; &gt; <br>
&gt; &gt; Regards,<br>
&gt; &gt; Suavi<br>
&gt; &gt; <br>
&gt; &gt; <br>
&gt; &gt; <br>
&gt; &gt; At 10:03 PM 8/11/2005, you wrote:<br>
&gt; &gt; Hi, <br>
&gt; &gt; <br>
&gt; &gt; Can any one help? I would like to use the IF control
statement in row
      <br>
&gt; level <br>
&gt; &gt; trigger. I do not find any detail related to it in manual and
on web<br>
&gt; &gt; site.If any<br>
&gt; &gt; have Idea about it please post me a example.<br>
&gt; &gt; <br>
&gt; &gt; Waiting for your reply.
      <br>
&gt; &gt; <br>
&gt; &gt; Thanks and&nbsp; Regrds,<br>
&gt; &gt; <br>
&gt; &gt; Sube Singh<br>
&nbsp;</blockquote>
    </span></div>
  </blockquote>
  </div>
  <br>
</blockquote>
</body>
</html>


Mime
View raw message