🚀 go-pugleaf

RetroBBS NetNews Server

Inspired by RockSolid Light RIP Retro Guy

Article View: bit.databases.mssql-l
Article #71

sql triggers on oracle

#71
From: michael s cathcart
Date: Sun, 09 Apr 2000 00:00
48 lines
1827 bytes
I am having problems creating triggers in sql on Oracle.  I tried to enter
them the way that the book lists: This is the trigger I am trying to
create:
	
CREATE TRIGGER triggername
INSTEAD OF UPDATE OF class ON Ships
REFERENCING
old_table AS oldtable,
new_table AS newtable
WHEN(newtable.class = (SELECT class FROM Classes)
)
DELETE FROM Ships
WHERE(name, class, launched) IN oldtable;
INSERT INTO Ships
(SELECT * FROM newtable);

This doesn't work on our system.  Someone told me to use pl/sql syntax to
create it and they gave me an example as follows:

CREATE TRIGGER triggername
AFTER UPDATE OF class ON Ships
FOR EACH ROW
/*start pl/sql*/
BEGIN 
INSERT INTO Ships	
// whatever you want to insert....
END;
/

Okay I see how this works but for my triggers I need to use the keyword
INSTEAD instead of AFTER.  When I do this:

CREATE TRIGGER triggername
INSTEAD OF UPDATE OF class ON Ships
.
.
.
it gives me an error that says this

INSTEAD OF UPDATE OF class ON Ships
                  *
invalid column for trigger type.

?? I have no clue what this means.
I just need to be able to enter this trigger and it work.  If I need to
use some sort pl/sql syntax that's fine.  If anyone knows anyway I can
create this trigger and a few more like it could you please help me out.
Thanks.
Technical Details
Message-ID: <Pine.OSF.4.21.0004091642050.21016-100000@bloodstone.cs.sc.edu>
Path: arc1-ca.pugleaf.net!unknown!not-for-mail

Show Full Headers
Complete Article Headers
X-Google-Language: ENGLISH,ASCII-7-bit
X-Google-Thread: f6611,3dc7824af2093852,start
X-Google-Attributes: gidf6611,public
From: michael s cathcart <mscathc@cs.sc.edu>
Subject: sql triggers on oracle
Date: 2000/04/09
Message-ID: <Pine.OSF.4.21.0004091642050.21016-100000@bloodstone.cs.sc.edu>
X-Deja-AN: 608839181
Organization: University of South Carolina - Columbia - Computer Science
Content-Type: TEXT/PLAIN; charset=US-ASCII
Mime-Version: 1.0
Newsgroups: bit.databases.mssql-l