General
cancel
Showing results for 
Search instead for 
Did you mean: 

change trigger to insert date in CDT

SOLVED
Go to solution
Ratzie
Super Advisor

change trigger to insert date in CDT

I have a trigger that I need to change.
Our database is in GMT, I need to have the date displayed as CDT.

I know how to convert a select...
SELECT 'Now => ' || TO_CHAR (new_time (SYSDATE, 'GMT', 'CDT'), 'DD-MON-YY HH24:MI:SS')
FROM dual


But, how whould I change the trigger below to have ROWCREH in CDT.
-----------------------
CREATE OR REPLACE TRIGGER MQM.SET_EAIERRJNLSEQN
BEFORE INSERT
ON MQM.TRANSACTIONAUDIT
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
newseqn NUMBER (15);
newcurdate DATE;
BEGIN
SELECT seq_eaierrjnlseqn.NEXTVAL, SYSDATE
INTO newseqn, newcurdate
FROM DUAL;

:NEW.eaierrjnlseqn := newseqn;
:NEW.rowcreh := newcurdate;
END;
3 REPLIES
Frank de Vries
Respected Contributor
Solution

Re: change trigger to insert date in CDT

I don't have your table,
but othewise it seems to work:

CREATE OR REPLACE TRIGGER MQM.SET_EAIERRJNLSEQN
BEFORE INSERT
ON MQM.TRANSACTIONAUDIT
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
newseqn NUMBER (15);
newcurdate DATE;
BEGIN
SELECT seq_eaierrjnlseqn.NEXTVAL,TO_CHAR(SYSDATE, 'GMT', 'CDT')
INTO newseqn, newcurdate
FROM DUAL;

:NEW.eaierrjnlseqn := newseqn;
:NEW.rowcreh := newcurdate;
END;

Look before you leap
Ratzie
Super Advisor

Re: change trigger to insert date in CDT

Nope that did not work, I got an error...
(see error below)
I have quasi fix if by doing:
(but to me I am hacking this, and not really converting time to CDT.
SYSDATE-(1/4.8)

**********
CREATE OR REPLACE TRIGGER MTS_WASCONN.SET_EAIERRJNLSEQN
BEFORE INSERT
ON MTS_WASCONN.TRANSACTIONAUDIT
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
newseqn NUMBER (15);
newcurdate DATE;
BEGIN
SELECT seq_eaierrjnlseqn.NEXTVAL, SYSDATE-(1/4.8)
INTO newseqn, newcurdate
FROM DUAL;

:NEW.eaierrjnlseqn := newseqn;
:NEW.rowcreh := newcurdate;
END;
****************

Error from GMT, CDT
ORA-12702: invalid NLS parameter string used in SQL function
ORA-06512: at "MQM.SET_EAIERRJNLSEQN", line 5
ORA-04088: error during execution of trigger 'MQM.SET_EAIERRJNLSEQN'
Frank de Vries
Respected Contributor

Re: change trigger to insert date in CDT

"ORA-12702: invalid NLS parameter string used in SQL function"

This has something to do with NLS setting between your client-session and server setttings.

You can diagnose:
Your database characher set is:

SQL> select userenv('LANGUAGE') from dual;

compare with

SELECT *
FROM v$nls_parameters
WHERE parameter LIKE '%CHARACTERSET%';

Look before you leap