Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Trigger equivalent to SQLServer Trigger
Oracle Trigger equivalent to SQLServer Trigger [message #213303] Wed, 10 January 2007 04:29 Go to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Hi Everybody,
I tried to write an Oracle Trigger that has the same functionality of an SqlServer Trigger but i could not solve the Code of the 'IF Statment' ..can anyone help??

The Trigger is:
==============
CREATE TRIGGER TRG_ForumPosts
ON dbo.csForumPosts
FOR INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON
IF UPDATE(nrTopic) OR (EXISTS(select 1 from deleted) AND NOT EXISTS(select 1 from inserted))
BEGIN ...........


Thanks in Advance,
bahaa

Re: Oracle Trigger equivalent to SQLServer Trigger [message #213310 is a reply to message #213303] Wed, 10 January 2007 04:50 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here is an example of how it looks like on Oracle:
CREATE TRIGGER trg_test
BEFORE UPDATE OR INSERT OR DELETE ON test
FOR EACH ROW
BEGIN
  IF UPDATING THEN
     dbms_output.put_line('Updating');
  ELSIF DELETING THEN
     dbms_output.put_line('Deleting');
  ELSIF INSERTING THEN
     dbms_output.put_line('Inserting');
  END IF;
END;
/

If you can do something with provided example, fine. If not (or you need additional explanation), say so.
Re: Oracle Trigger equivalent to SQLServer Trigger [message #213315 is a reply to message #213310] Wed, 10 January 2007 05:03 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Thank you for your replay,
this trigger has another part like this:
CREATE TRIGGER TRG_ForumPosts
ON dbo.csForumPosts
FOR INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON
IF UPDATE(nrTopic) OR (EXISTS(select 1 from deleted) AND NOT EXISTS(select 1 from inserted))
BEGIN
-- PRINT 'column nrTopic affected by inserted, updated or delete'

DECLARE @nr_topic int
DECLARE @cnt int
DECLARE @nr_lastpost int
DECLARE cur_topics CURSOR
FOR
SELECT c.Nr
, (SELECT count(*) FROM csForumPosts nfp WHERE nfp.Status=1 AND nfp.nrTopic=c.Nr) as cnt
, (SELECT max(Nr) FROM csForumPosts nfp2 WHERE nfp2.Status=1 AND nfp2.nrTopic=c.Nr) as lastpost
FROM Contents c
WHERE c.Nr IN (
SELECT nrTopic FROM inserted
UNION
SELECT nrTopic FROM deleted
)................

So How to solve the UNION part of the Subquery??

Thanks In Advance,
bahaa

Re: Oracle Trigger equivalent to SQLServer Trigger [message #213323 is a reply to message #213315] Wed, 10 January 2007 05:42 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
UNION is valid operator in PL/SQL (which trigger, actually, is). The only thing you have to take care about is that SELECT must have an INTO clause. However, as product of an UNION operation isn't only one value but more than that, you might have to use refcursor. Here is an example of such a code (I didn't create a trigger but procedure, but - as both are PL/SQL procedures, I hope you'll get the point):
SQL> CREATE OR REPLACE PROCEDURE Prc_Test (par_out IN OUT sys_refcursor) IS
  2  BEGIN
  3    OPEN par_out FOR
  4      SELECT dname FROM DEPT WHERE deptno <= 20
  5      UNION
  6      SELECT dname FROM DEPT WHERE deptno > 20;
  7  END;
  8  /

Procedure created.

SQL> var dpt refcursor;
SQL> exec prc_test (:dpt);

PL/SQL procedure successfully completed.

SQL> print dpt

DNAME
--------------
ACCOUNTING
OPERATIONS
RESEARCH
SALES

SQL>


[EDIT] Bah, never mind me ... now I noticed that your UNION is part of a subquery. Even better - you don't have to do anything about it (as far as UNION is concerned).

But, what does "select nr from inserted" does? What is "inserted"?

[Updated on: Wed, 10 January 2007 05:44]

Report message to a moderator

Re: Oracle Trigger equivalent to SQLServer Trigger [message #213333 is a reply to message #213323] Wed, 10 January 2007 06:06 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Hi Again,
The UNION subquery Checks the deleted records and the inserting records for the value of the 'nrTopic'..

i made an Oracle version of this query but i could not solve the UNION part of the subquery..My version is:
=========================================
DECLARE
ItExist0 INTEGER := 0;
ItExist1 INTEGER := 0;
-- PRINT 'column nrTopic affected by inserted, updated or delete'
nr_topic INT;
cnt INT;
nr_lastpost INT;
TYPE REF_cur_topics IS REF CURSOR;
cur_topics REF_cur_topics;

BEGIN
IF DELETING THEN
SELECT 1 INTO ItExist0 FROM DUAL;
ELSIF INSERTING THEN
SELECT 1 INTO ItExist1 FROM DUAL;

IF UPDATING (nrTopic) OR( ItExist0 != 0 AND NOT ItExist1 != 0) THEN
OPEN cur_topics FOR
SELECT c.Nr,(SELECT COUNT(*)
FROM csForumPosts nfp
WHERE nfp.Status = 1
AND nfp.nrTopic = c.Nr)as cnt,(SELECT MAX(Nr)
FROM csForumPosts nfp2
WHERE nfp2.Status = 1
AND nfp2.nrTopic = c.Nr)as lastpost
FROM Contents c
WHERE c.Nr IN( SELECT nrTopic
FROM DUAL
UNION
SELECT nrTopic
FROM DUAL ); ........

The double select from DUAL is not a good solution for the UNION problem..??

Thanks in Advance,
Bahaa
Re: Oracle Trigger equivalent to SQLServer Trigger [message #213430 is a reply to message #213303] Wed, 10 January 2007 14:12 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Hi,
I do not think it is good to publish here sqlserver code as it seems nobody here knows its meaning. It would be better to describe the aim you want to achieve in words.
As I see your posts, it seems to me you want to get the value of the nrTopic column. inserted and deleted seem to be structures which hold the row values in sqlserver. In Oracle, these values are stored in OLD and NEW records. So I would write something like
CREATE TRIGGER TRG_ForumPosts
BEFORE INSERT OR UPDATE OF nrTopic OR DELETE
ON dbo.csForumPosts
FOR EACH ROW
DECLARE
nr_topic_old integer;
nr_topic_new integer;
cnt integer;
nr_lastpost integer;
TYPE REF_cur_topics IS REF CURSOR;
cur_topics REF_cur_topics;
BEGIN
-- do not know what is in :new in delete and in :old in insert
-- i am afraid it contains nulls which is not wise to use
-- you can check it yourself as i do not have access to Oracle now
IF DELETING THEN
  nr_topic_old := :old.nrTopic;
  nr_topic_new := :old.nrTopic;
ELSIF INSERTING THEN
  nr_topic_old := :new.nrTopic;
  nr_topic_new := :new.nrTopic;
ELSE
  nr_topic_old := :old.nrTopic;
  nr_topic_new := :new.nrTopic;
END IF;

-- your commands, use nr_topic_old and nr_topic_new variables
OPEN cur_topics FOR
SELECT c.Nr,(SELECT COUNT(*)
FROM csForumPosts nfp
WHERE nfp.Status = 1
AND nfp.nrTopic = c.Nr)as cnt,(SELECT MAX(Nr)
FROM csForumPosts nfp2
WHERE nfp2.Status = 1
AND nfp2.nrTopic = c.Nr)as lastpost
FROM Contents c
WHERE c.Nr in ( nr_topic_new, nr_topic_old );
END;
/

Consult the documentation for correct syntax (as you are quite far from it).
If it is not the solution you want describe your request in words.
Re: Oracle Trigger equivalent to SQLServer Trigger [message #213524 is a reply to message #213430] Thu, 11 January 2007 02:07 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
'I do not think it is good to publish here sqlserver code as it seems nobody here knows its meaning. It would be better to describe the aim you want to achieve in words.'

I Thought when somebody search for a solution and need an advice about some code in oracle he need to show the code directly..

if i knew that Oracle use :new.columnname,:Old.columnname to solve the temporary memory tables of SqlServer i would not send you the question ..

i will test the idea you send to see if it is applicable to the problem of the trigger.

Thanks in Advance,
bahaa
Re: Oracle Trigger equivalent to SQLServer Trigger [message #213533 is a reply to message #213524] Thu, 11 January 2007 03:04 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
The trouble is that not many of us knew what this was supposed to do (I certainly didn't):

IF UPDATE(nrTopic) OR (EXISTS(select 1 from deleted) AND NOT EXISTS(select 1 from inserted))

Re: Oracle Trigger equivalent to SQLServer Trigger [message #213540 is a reply to message #213524] Thu, 11 January 2007 03:29 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
I do not think it is good to publish here SQLSERVER code as it seems nobody here knows its meaning.

You are welcome to show your ORACLE code. However the code you provided was not the case as even its syntax was wrong. If you would try to compile and corrected its syntax, you would find out no inserted and deleted tables/views are available in Oracle trigger. This would be a good starting point for your question (in sqlserver trigger I use inserted table/view for getting the row values, how to get these values in Oracle trigger?)
Re: Oracle Trigger equivalent to SQLServer Trigger [message #213541 is a reply to message #213533] Thu, 11 January 2007 03:37 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
You have actually right this line code is complicated a little bit..well i will use pseudo code as well as Oralce syntax:

In case the Trigger now updating the 'nrTopic' value then:===>

IF UPDATING(nrTopic) OR (IF 'nrTopic' Exist in Deleted records 'that have been deleted before' ) AND NOT (if 'nrTopic' Exist in inserted records 'that have been inserted before')Then....


The problem is that i couldn't figure it out how to determine these deleted or inserted records that sqlserver keep in temporary memory tables ..as for Oracle using :new.columnname or :old.columnname may refer to the same field value 'nrTopic' that has been updated rather than the newly inserted value..in case of the first part of the code:
If UPDATING(nrTopic) OR (IF nrTopic =:old.nrTopic) AND NOT (IF nrTopic =:new.nrTopic)..it seems to me not the same..

Thanks in Advance,
bahaa
Re: Oracle Trigger equivalent to SQLServer Trigger [message #213554 is a reply to message #213541] Thu, 11 January 2007 04:21 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
As far as I know no such structures are available in Oracle. Concurrent sessions are managed as described here.
Briefly: transaction starts with the first executable sql statement and ends with COMMIT (confirmation) or ROLLBACK (cancellation). Data definition statements (DDL, eg. CREATE TABLE) end transaction too. Until the transaction ends other sessions do not see changes made by the transaction. However the transaction may lock data (eg. UPDATE blocks the rows it modifies), so other transactions updating the same rows have to wait till the locking transactions ends (COMMIT or ROLLBACK).
Re: Oracle Trigger equivalent to SQLServer Trigger [message #213555 is a reply to message #213541] Thu, 11 January 2007 04:28 Go to previous message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
bahy91 wrote on Thu, 11 January 2007 03:37
The problem is that i couldn't figure it out how to determine these deleted or inserted records that sqlserver keep in temporary memory tables

I don't think ORacle has any equivalent of that. You might need to explain the business requirement, and we can try to figure out how to implement it in Oracle.
Previous Topic: URGENT - How to find the master table for a view
Next Topic: Performance Issue
Goto Forum:
  


Current Time: Thu Dec 08 12:23:31 CST 2016

Total time taken to generate the page: 0.09784 seconds