trigger Help

[nathanharcup]

Hi All,
I am try to convert the below SQL trigger into PLSQL, can anyone help?

CREATE TRIGGER copy on table1 for INSERT
AS
BEGIN
IF UPDATE (COLUMN1)

UPDATE table1
SET column2 = t.column2
SET column3 = t.column3
SET column4 = t.column4
FROM table1 n, table2 t
WHERE t.column1 = n.column1
end

 

[Dagon]

I don't know what you mean. Triggers are written in PL/SQL, so how can you convert it to PL/SQL ?

 

[nathanharcup]

sorry, the above trigger is written for tsql

It builds OK but with compliation errors, mainly the UPDATE command fails.

 

[Dagon]

What is the error you are getting ?

 

[SantaMufasa]

Nathan,

To successfully create your trigger in Oracle's PL/SQL, you will need to make a few syntactical adjustments. Rather than my recapping Oracle PL/SQL's TRIGGER syntax here, it will more efficient (and satisfying for you) if you reconcile your code to a PL/SQL trigger-syntax diagram.

Amongst the more obscure issues to look out for in your code are:

1) Semi-colons must terminate each and every speparate statement in a PL/SQL block including (in your case) the end of IF statements (following your missing "END IF;" clause, UPDATE statements (at the end of the last WHERE clause), and the "END;" of the PL/SQL block itself.

2) "IF" statements must terminate with and "END IF;" clause

3) If you are running the code in SQL*Plus, then the PL/SQL block must terminate with a "/" on a line of its own, which instructs SQL*Plus to release the code to the PL/SQL interpreter to compile/interpret the preceding block.

4) Oracle UPDATE statements are more complicated than the example you assert, above. If you wish to UPDATE a table from contents of another table, you must embed a SELECT subquery in the SET clause. My attempt at fixing your UPDATE statement might be:

...UPDATE table1 n
SET (column2, column3, colum4) = 
(SELECT t.column2, t.column3, t.column4
   FROM table2 t
  WHERE t.column1 = n.column1);
...

5) I am not aware of an Oracle function that supports "UPDATE (COLUMN1)" syntax.

So, combined with TRIGGER-syntax issues, your fixing the above issues should help, as well.

Let us know if/when you get the code running and your finished code format.


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[nathanharcup]

I have amended the trigger as suggested, i get the error that end; is incorrect, can anyone see what I have done wrong?

declare name varchar2(30);
begin

select uname into name from table1;

if name = 'nathan'
then
update table1 n
set (column1, column2, column3)=
(select t.column1, t.column2, t.column3
from table2 t
where t.column4 = n.column4);
else
end if;
end;

 

[nathanharcup]

got this sorted by removing the t. from table 2 columns.


Thanks for all yur help

Nathan