Home » SQL & PL/SQL » SQL & PL/SQL » Issue with Trigger (9i)
Issue with Trigger [message #353228] Mon, 13 October 2008 00:58 Go to next message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member
Hi Guru's,

I need some help to make the Trigger working. If there is any changes (Insert or Update) on the table TEST1, I need to update the coloumn DFIELD of table DATE2. The below trigger does not give me any error but does not update the field DFIELD in table DATE2.

create or replace trigger XYZ
after insert or update on TEST1
begin
update DATE2
  SET DFIELD =sysdate;
end XYZ;


This works fine


I have changed the trigger as below with using newRow. But this one gives me below error.

create or replace trigger XYZ
after insert on TEST1
REFERENCING NEW AS newRow
for each row
begin
update DATE2
  SET DFIELD =:newRow.sysdate;
end XYZ;



Line # = 3 Column # = 18 Error Text = PLS-00049: bad bind variable 'NEWROW.SYSDATE'


Please help me as I had got help from this site before in past.
Thank in advance.
syd
Re: Issue with Trigger [message #353231 is a reply to message #353228] Mon, 13 October 2008 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
sysdate is not a field of newrow record.
Why did you change your previous working trigger?

In addition:
1/ The trigger update the whole table, is this you really want
2/ Don't do this in trigger, use a procedure to clearly update the 2 tables.

Regards
Michel

[Updated on: Mon, 13 October 2008 01:06]

Report message to a moderator

Re: Issue with Trigger [message #353236 is a reply to message #353228] Mon, 13 October 2008 01:18 Go to previous messageGo to next message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member
Hi Michel,

Thanks for your reply. OK I will concentrate on the first trigger and discuss that only in my correspondence.

The problem I am having is when i update the table 'TEST1' the coloumn DFIELD i table DATE2 has not been updated. Please advice.

Rgds
Syd
Re: Issue with Trigger [message #353237 is a reply to message #353236] Mon, 13 October 2008 01:21 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi,
i think the problem is very simple one. why don't you describe both the tables.
yours
dr.s.raghunathan
Re: Issue with Trigger [message #353240 is a reply to message #353228] Mon, 13 October 2008 01:25 Go to previous messageGo to next message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member
Hi Dr Raghu,
Please find below the information you has requested.

SQL> desc TEST1
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(20)
USERID NOT NULL VARCHAR2(20)
DATEFIELD DATE

SQL> desc DATE2
Name Null? Type
----------------------------------------- -------- ----------------------------
DFIELD NOT NULL DATE
USERID VARCHAR2(20)

SQL>

Thanks for help
Syd
Re: Issue with Trigger [message #353241 is a reply to message #353240] Mon, 13 October 2008 01:29 Go to previous messageGo to next message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member
for your Convenience
SQL> desc TEST1
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(20)
USERID NOT NULL VARCHAR2(20)
DATEFIELD DATE

SQL> desc DATE2
Name Null? Type
----------------------------------------- -------- ----------------------------
DFIELD NOT NULL DATE
USERID VARCHAR2(20)

SQL>



Syd
Re: Issue with Trigger [message #353243 is a reply to message #353236] Mon, 13 October 2008 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The problem I am having is when i update the table 'TEST1' the coloumn DFIELD i table DATE2 has not been updated.

Did you commit?

Once again don't do that.

Regards
Michel
Re: Issue with Trigger [message #353244 is a reply to message #353228] Mon, 13 October 2008 01:33 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Even I have the same Opinion of Michel for updating the DATE2 along with the process of DML Operations on TEST1 and not as a part of trigger .

Anyway I don't thing any reason of trigger for not getting fired on DML on TEST1 . Provide us the test case ( Create and insert script) and prove.

I hope this is just for your training purpose .

Thumbs Up
Rajuvan.

[Updated on: Mon, 13 October 2008 01:34]

Report message to a moderator

Re: Issue with Trigger [message #353245 is a reply to message #353240] Mon, 13 October 2008 01:34 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi,

hope your userid in test1 is unique.
update date2 set dfield = (write the select statement using userid of test1). complete it you will be successful.
yours
dr.s.raghunathan
Re: Issue with Trigger [message #353254 is a reply to message #353245] Mon, 13 October 2008 01:46 Go to previous messageGo to next message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member
Hi Dr Raghu, Rajuvan and Mitch.
Thanks for helping me around.

Raghu I have chaned trigger have a look, Still the table DATE2 is not been updated.

SQL> create or replace trigger trg_mv_dept
  2        after insert or update on LDAPTEST
  3        for each row
  4      begin
  5  update INSERTLDAPDATE
  6   SET DATEFIELD =sysdate
  7  WHERE USERID = :NEW.USERID;
  8  END;
  9  /

Trigger created.



Raju,
I am using OEM to insert the row and see it has been added. next time I will add using script and update you too.

Is there is any template of procedure to do as I want to achive.

Thanks again

Syd
Re: Issue with Trigger [message #353255 is a reply to message #353228] Mon, 13 October 2008 01:50 Go to previous messageGo to next message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member
Hi Rajuvan,

INSERT INTO "SCOTT"."TEST1" ("USERNAME" ,"USERID" ,"DATEFIELD"
    ) 
    VALUES 
('nick' ,'13' ,TO_DATE('', 'dd-Mon-yyyy HH:MI:SS AM') 
    );
commit;


Rgds
SYD
Re: Issue with Trigger [message #353256 is a reply to message #353254] Mon, 13 October 2008 01:50 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi,

now i am confused with your sysdate concept. I thought whenever you are inserting test1 with userid and datefield, the date available in datefeild should be get updated in date2(dfield).
since you have used statement trigger with after.
yours
dr.s.raghunathan

Re: Issue with Trigger [message #353257 is a reply to message #353256] Mon, 13 October 2008 01:55 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi,
one more clue..
try writing trigger if inserting then...
if updating then...
if deleting then...
in after statement trigger.

yours
dr.s.raghunathan
Re: Issue with Trigger [message #353258 is a reply to message #353257] Mon, 13 October 2008 02:00 Go to previous messageGo to next message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member
Thanks Dr.Raghu,

I will try to write trigger as IF --- END statement.
Thanks for the clue.

Rgds
SYD
Re: Issue with Trigger [message #353264 is a reply to message #353228] Mon, 13 October 2008 02:08 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

What I was asking you to give the full test code to prove that trigger is not working .

ie ,

drop the tables first .

1. Create and insert scripts for two tables
2. Trigger code used
3. DML code for updating the TEST1 tables
4. Select from DATE2 to show that date field is not changed.

Do all these in SQL*PLUS single session .

It may take some time to do all these. But will help others to understand the issues if any.

Also dont forget to format the full code.

Thumbs Up
Rajuvan.
Re: Issue with Trigger [message #353265 is a reply to message #353228] Mon, 13 October 2008 02:10 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member



Quote:
SQL> create or replace trigger trg_mv_dept
2 after insert or update on LDAPTEST
3 for each row
4 begin
5 update INSERTLDAPDATE
6 SET DATEFIELD =sysdate
7 WHERE USERID = :NEW.USERID;
8 END;
9 /



In your query the Trigerring table is LDAPTEST instead of TEST1 Smile

Thumbs Up
Rajuvan.
Re: Issue with Trigger [message #353437 is a reply to message #353228] Mon, 13 October 2008 22:22 Go to previous messageGo to next message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member
Thanks to all of you for help and suggestions.
I have changed the trigger to before insert trigger and on the same table as below and it is working fine.
create or replace trigger XYZ
before insert or update on TEST
for each row
begin
  :NEW.DFIELD := sysdate;
end ;


Keep helping each other guys. Good on all our DBA's community.
SYD
Re: Issue with Trigger [message #353439 is a reply to message #353228] Mon, 13 October 2008 22:29 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Actually doesn't matter whether it is Before Or After insert trigger. Both should work fine .

Thumbs Up
Rajuvan.

[Updated on: Mon, 13 October 2008 22:29]

Report message to a moderator

Re: Issue with Trigger [message #353442 is a reply to message #353228] Mon, 13 October 2008 22:35 Go to previous messageGo to next message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member
Thanks Rajuvan,

Actually I can't make after trigger working as it is not taking :new.sysdate.

what i want to achive is whenever anything update or insert happens on the table TEST. DFIELD should be updated with 'sysdate'.
BUt I think if I make after trigger working that is better. As the DFILED should be updated after any changes in other fields.
But I can't make after trigger work.
if you have any suggestion to fix after trigger. Please advice.
Regards
SYD
Re: Issue with Trigger [message #353447 is a reply to message #353228] Mon, 13 October 2008 22:44 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member



It is As simple as changing the 'before' to 'After' in your last trigger code .

Btw, you can't refer the :new.sysdate as sysdate is not field in the triggering table. just use Sysdate.

Thumbs Up
Rajuvan.
Re: Issue with Trigger [message #353450 is a reply to message #353228] Mon, 13 October 2008 22:55 Go to previous messageGo to next message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member
Hi Rajuvan,

when i use after trigger I am getting the below error.

SQL> create or replace trigger XYZ
  2  after insert or update on TEST
  3  for each row
  4  begin
  5    :NEW.DFIELD := sysdate;
  6  end;
  7  /
create or replace trigger test
                          *
ERROR at line 1:
ORA-04084: cannot change NEW values for this trigger type


I have searched the google and found

ORA-04084: cannot change NEW values for this trigger type
Cause: New trigger variables can only be changed in before row insert or update triggers.
Action: Change the trigger type or remove the variable reference.



rgds
SYD
Re: Issue with Trigger [message #353453 is a reply to message #353228] Mon, 13 October 2008 23:06 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

ohh Yes.. You are right .

Btw, i your previous post you were trying to update DATE2.DFIELD while in the last One , you are updating the TEST.DFILE only.

Anyway your First code will work .

create or replace trigger XYZ
after insert or update on TEST1
begin
update DATE2
  SET DFIELD =sysdate;
end XYZ;


Otherwise ,
Prove its wrong . ( As I suggested you earlier)

Thumbs Up
Rajuvan.
Re: Issue with Trigger [message #353463 is a reply to message #353228] Tue, 14 October 2008 00:04 Go to previous messageGo to next message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member
Hi Rajuvan,
You are right that code will work. But now I have to add a column in the same table and update that column that is the reason I am using another trigger.
Thanks for your help.

Rgds
SYD
Re: Issue with Trigger [message #353464 is a reply to message #353228] Tue, 14 October 2008 00:05 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
You cannot update the :new.xxx column because the trigger is an AFTER row trigger.

the idea of updating :new.xxx is that you want to change the value of some column before it is written to the table.

In an after row trigger, data has already been updated on the table so how did you expect changing :new.xxx at that point would make its way to the row on the table?

You need a before row trigger.

Also, this entire thread does not really work for me and the reason is simple. In order to get a real answer to your issue, you need to do some basic things. For example, you should have provided in your first post the following:

Quote:
1) table create SQL for all tables
2) trigger create SQL
3) insert SQL for any sample data needed
4) insert SQL for the actual events to be tested
5) select SQL for before and after insert
6) your run of all the above to show the problem

with this information, many people here would have given you an immediate solution to whatever it is that hurts. Indeed, in putting this together you would most likely have figured out what is wrong yourself.

I would suggest the following:

You start over and do the six things above, then post it here and tell us what you have found.

Kevin
Re: Issue with Trigger [message #353500 is a reply to message #353228] Tue, 14 October 2008 01:03 Go to previous messageGo to next message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member
Hi Kevin,

Thanks for nice explanation. Appreciate your help. Yes I have written before trigger and it works. If I find time I will do the test per your and Rajuvan suggest and update you guys.
Thanks
Rgds
Syd
Re: Issue with Trigger [message #353666 is a reply to message #353228] Tue, 14 October 2008 09:06 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Thanks, glad you are moving forward.

Good luck, Kevin
Re: Issue with Trigger [message #353979 is a reply to message #353228] Thu, 16 October 2008 00:03 Go to previous messageGo to next message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member
Hi All the one who helped me.

I am using sysdate in my trigger to compare the date/time.
but when i query sysdate in sqlplus its not giving me time as below.
SQL> select sysdate from dual;

SYSDATE
---------
16-OCT-08

 
SQL> select TO_CHAR(sysdate,'DD-Mon-YYYY HH24:MI:SS') FROM dual;

TO_CHAR(SYSDATE,'DD-
--------------------
16-Oct-2008 15:58:53



In the script I have written in perl is using sysdate to distinguish between the two times.
I want to use only sysdate to give me day and time.
the column datatype is DATE.
Please advice.
SYD
Re: Issue with Trigger [message #353982 is a reply to message #353228] Thu, 16 October 2008 00:20 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Sysdate always is having time portion with it. It is just a matter of altering nls parameter for display .


SQL> select sysdate from dual;

SYSDATE
---------
16-OCT-08

SQL> alter session set nls_date_format= 'DD-MON-YY HH24:MI:SS'
  2  ;

Session altered.

SQL> select sysdate from dual;

SYSDATE
------------------
16-OCT-08 10:45:50


Thumbs Up
Rajuvan.
Re: Issue with Trigger [message #353990 is a reply to message #353228] Thu, 16 October 2008 00:47 Go to previous message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member
Thanks again Rajuvan,

I am working on the issue to syncronize the table with Active Directory.
The script is running to syncronize the AD with table every 15 minutes.
I am bit confused how to write the code.
the code should be like

select * from table
where timemodified > 'here the time of the script as we want to run the script every 15 minutes' if the script runs at 1am then again at 1.15am and check the table coloumn.

Please help if someone done like this.
Thank in advance
SYD
Previous Topic: Missing weeks should contain the value prior to the one that is present
Next Topic: Where the cursor's (implicit/explicit) result is stored...?
Goto Forum:
  


Current Time: Fri Dec 09 06:00:35 CST 2016

Total time taken to generate the page: 0.09448 seconds