Article View: bit.databases.mssql-l
Article #71sql triggers on oracle
From: michael s cathcart
Date: Sun, 09 Apr 2000 00:00
Date: Sun, 09 Apr 2000 00:00
48 lines
1827 bytes
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-mailShow 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