Trigger question 2

[kmfna]

Hello all,

I am working on some triggers to help keep up with Adds/Deletes/Updates, our business requires that we send data loads out to people, and an initial file, followed by an adds/deletes/updates file makes the most sense (as we will only have to send 4+ gig files only once, then can send much smaller 30meg or so files later on). Anyway, here are the triggers, that I am working on, but I keep getting an error stating that the table does not exist...

Trigger Code:

CREATE OR REPLACE TRIGGER LOG_UPDATES
	BEFORE UPDATE of LAST_NAME ON MY_TABLE
	REFERENCING old AS pre new AS post
	DECLARE
		var_OS_USER VARCHAR2(50);
		for each row
			BEGIN
				SELECT osuser 
				INTO var_OS_USER
				FROM v$session
				WHERE audsid=userenv('sessionid');

				if (post.LAST_NAME != pre.LAST_NAME)
				then
					INSERT INTO CHANGELOG 
                     VALUES(pre.PK_UNIQUE_ID, sysdate, var_OS_USER,'LAST_NAME', post.LAST_NAME, pre.LAST_NAME);
				end if
			END;
			
CREATE OR REPLACE TRIGGER LOG_ADDS
	BEFORE INSERT ON MY_TABLE
	DECLARE
	var_OS_USER VARCHAR2(50);
	BEGIN
		SELECT osuser 
		INTO var_OS_USER
		FROM v$session
		WHERE audsid=userenv('sessionid');
		
		INSERT INTO ADDLOG
		VALUES(new.PK_UNIQUE_ID, sysdate, var_OS_USER);
	END;
	
CREATE OR REPLACE TRIGGER LOG_DELETES
	BEFORE DELETE ON MY_TABLE
	DECLARE
	var_OS_USER VARCHAR2(50);
	BEGIN
		SELECT osuser 
		INTO var_OS_USER
		FROM v$session
		WHERE audsid=userenv('sessionid');
		
		INSERT INTO DELLOG
		VALUES(old.PK_UNIQUE_ID, sysdate, var_OS_USER);
	END;

For some reason, it seems like most of the problems are with understanding what the v$session table is. Strangely, I can query from it in SQL*plus, but the trigger doesn't recognize it.

Any ideas?

Thanks in advance,
Kevin

- "The truth hurts, maybe not as much as jumping on a bicycle with no seat, but it hurts.

 

[SantaMufasa]

Kevin,

Try prefixing your table references with owners...in the case of your error, "SYS.V$SESSION".

[And next time, to ensure that we are helping you and troubleshooting at our best, could you please post a copy-and-paste of your SQL*Plus line-numbered listing of your PL/SQL code (by typing "list" at the SQL> prompt following your receipt of your error message, and also a copy-and-paste of the resulting error message.]

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.

 

[sem]

You should be also granted explicit (not via role) right to select from sys.v_$session. Be careful and type it correctly to avoid annoying ORA-02030 error

Regards, Dima

http://seminihin.narod.ru

 

[kmfna]

Hey guys,

Thanks for the quick response... I tried altering the script to reference the owner, but still encountered the same problem...here are the results from SQL*plus:

Wrote file afiedt.buf

  1  CREATE OR REPLACE TRIGGER LOG_DELETES
  2  BEFORE DELETE ON MAIN_PHS
  3  DECLARE
  4  var_OS_USER VARCHAR2(50);
  5  BEGIN
  6  SELECT osuser
  7  INTO var_OS_USER
  8  FROM "SYS.v$session"
  9  WHERE audsid=userenv('sessionid');
 10  INSERT INTO "kevin.DELLOG"
 11  VALUES(old.PK_UNIQUE_ID, sysdate, var_OS_USER);
 12* END;
SQL> /

Warning: Trigger created with compilation errors.

SQL> sho err
Errors for TRIGGER LOG_DELETES:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/1      PL/SQL: SQL Statement ignored
6/6      PL/SQL: ORA-00942: table or view does not exist
8/1      PL/SQL: SQL Statement ignored
8/13     PL/SQL: ORA-00942: table or view does not exist

this is only running the log delete trigger (I couldn't run the others, since we are loading data, right now).

Thanks again,
Kevin

- "The truth hurts, maybe not as much as jumping on a bicycle with no seat, but it hurts.

 

[sem]

1. Double quotes make Oracle look for objects whose names use mixed cases, obviously there's no. Why do you use double quotes?

2. Scroll up a bit, read it again and follow advices more carefully

Regards, Dima

http://seminihin.narod.ru

 

[SantaMufasa]

Actually, the double quotes that Kevin used are probably my fault since I quoted sys.v$session in my suggestion, when, in fact, I should have probably said:

[b]sys[/b].v$session[/quote]...to avoid all ambiguity.

Sorry, Kevin, I'll take the punishment for you.[wink]

 [santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[[URL unfurl="true"]www.dasages.com:[/URL] Providing low-cost remote Database Admin services]
Click here to join [url=http://tek-tips.com/threadminder.cfm?pid=1426]Utah Oracle Users Group on Tek-Tips[/url] if you use [b]Oracle[/b] in [b]Utah[/b] USA.

 

[kmfna]

haha...no sweat Mufasa..you're still the man. I totally knew better, but didn't think about it....this, is what happens when you blindly follow someone else and don't think for yourself...let everyone learn from my mistake.

Besides, that wasn't my final problem....I made a couple of total jackass mistakes.... but, should anyone want to do the same thing and avoid my mistakes, I'll post the working script on here:

CREATE OR REPLACE TRIGGER NPI_UPDATES
BEFORE UPDATE of NPI ON MAIN_PHS
for each row
DECLARE
var_OS_USER VARCHAR2(30);
BEGIN
SELECT osuser
INTO var_OS_USER
FROM MY_SESSION_INFO;
if (:old.NPI != :new.NPI)
then
INSERT INTO kevin.LOG_UPDATE VALUES(:old.PK_UNIQUE_ID, sysdate, var_OS_USER,'NPI', TO_CHAR(:old.NPI), TO_CHAR(:new.NPI));
end if;
END;
/			
CREATE OR REPLACE TRIGGER LOG_ADDS
BEFORE INSERT ON MAIN_PHS
for each row
DECLARE
var_OS_USER VARCHAR2(30);
BEGIN
SELECT osuser 
INTO var_OS_USER
FROM MY_SESSION_INFO;
INSERT INTO kevin.LOG_ADD
VALUES(:new.PK_UNIQUE_ID, sysdate, var_OS_USER);
END;
/
CREATE OR REPLACE TRIGGER LOG_DELETES
BEFORE DELETE ON kevin.MAIN_PHS
for each row
DECLARE
var_OS_USER VARCHAR2(30);
BEGIN
SELECT osuser 
INTO var_OS_USER
FROM MY_SESSION_INFO;
INSERT INTO kevin.LOG_DELETE
VALUES(:old.PK_UNIQUE_ID, sysdate, var_OS_USER);
END;
/

And just so that everyone is clear on it, "MY_SESSION_INFO" is a view of the current user's session information. Here is the script for it.

CREATE VIEW MY_SESSION_INFO
AS
SELECT * 
FROM V$session
WHERE audsid = userenv('sessionid');
/
GRANT SELECT ON MY_SESSION_INFO TO PUBLIC;
/

Just for edification, here is the structure of the 3 different tables:

SQL> desc log_delete;
 FK_UNIQUE_ID                              NOT NULL NUMBER(10)
 DELETE_DATE                               NOT NULL DATE
 DELETE_USER                                        VARCHAR2(50)

SQL> desc log_update;
 FK_UNIQUE_ID                              NOT NULL NUMBER(10)
 CHANGE_DATE                               NOT NULL DATE
 CHANGE_USER                                        VARCHAR2(50)
 CHANGE_COLUMN                             NOT NULL VARCHAR2(30)
 OLD_VALUE                                          VARCHAR2(150)
 NEW_VALUE                                          VARCHAR2(150)

SQL> desc log_add;
 FK_UNIQUE_ID                              NOT NULL NUMBER(10)
 INSERT_DATE                               NOT NULL DATE
 INSERT_USER                                        VARCHAR2(50)

These are going to come in MAD HANDY for us, and hopefully someone else that is out there will find that it is useful, and will make use of it. And just as a side note for anyone that comes in here and takes a look at this, you will probably want to duplicate the first trigger for each column you want to watch. You could do this on a record level, but I wanted to do it this way, just in case we add any new columns or rename existing columns. The Log_Add and Log_Delete should be ok, though.

Anyway, thanks to Mufasa and Sem for the help, you guys rock...watch for the falling stars.

- "The truth hurts, maybe not as much as jumping on a bicycle with no seat, but it hurts.

 

[Beilstwh]

You have a problem with your logic. You are using ld.PK_UNIQUE_ID as an identifier for the row. However, you have nothing in the table to determine the order of actions on your file. Say you have made two modifications to a particular column, which one does the client update first. Or if you have changes to the row and then you delete it.Create a single sequence and add it into all three log tables. The sequence will increment by one everytime it is used and will UNIQUELY identify what order your modifications need to be done. If truth be told, I would make a single log file with a status flag to indicate UPDATE,DELETE,OR INSERT to keep everything together.

Bill
Oracle DBA/Developer
New York State, USA