Conference orarep::nomahs::rdb_60

5074.0. "Date assignment in triggers" by ORAREP::STKHLM::BELUNDBERG (Ohh, I like it...) Wed Feb 26 1997 04:35

	Hi

	DEC Rdb V6.0-1.

	I'm trying to define two triggers, but gets the following errors:

SQL> create trigger FAXES_INS_TRIGGER
     after insert on FAXES
        (insert into FAX_HISTORY
              ( FAX_NUM, DOCID, IN_TIME )
         values
              ( FAXES.FAX_NUM, FAXES.DOCID, CURRENT_TIMESTAMP )
        ) for each row;
%SQL-F-UNSDATASS, Unsupported date/time assignment from <Source> to IN_TIME

SQL> create trigger FAXES_DEL_TRIGGER
     before delete on FAXES
        (update FAX_HISTORY FH
            set OUT_TIME = CURRENT_TIMESTAMP
            where (FH.DOCID = FAXES.DOCID)
        ) for each row;
%SQL-F-UNSDATASS, Unsupported date/time assignment from <Source> to OUT_TIME

- - - - - - - 

	IN_TIME/OUT_TIME are of DATE VMS type:

Columns for table FAX_HISTORY:
Column Name                     Data Type        Domain
-----------                     ---------        ------
FAX_NUM                         CHAR(19)         PHONE_DOM
DOCID                           INTEGER          DEXDOC_ID_DOM
IN_TIME                         DATE VMS         TIMESTAMP_DOM
OUT_TIME                        DATE VMS         TIMESTAMP_DOM

- - - - - - -

	I'm a bit puzzled since you're allowed to set IN_TIME/OUT_TIME
	to CURRENT_TIMESTAMP in an insert/update statement:

SQL> insert into fax_history 
     values ('1',1,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP);
1 row inserted


	So how would the time stamp assign in the trigger look like to work?

	Regards
	Bengt B Lundberg
	Stockholm, Sweden

What is the dialect?

SET DIALECT 'SQL92'
or
SET DEFAULT DATE FORMAT 'SQL92'

makes all references to CURRENT_TIMESTAMP refer to a function of type
TIMESTAMP(2).

SET DIALECT 'SQL040'
or
SET DEFAULT DATE FORMAT 'VMS'

makes all references to CURRENT_TIMESTAMP refer to a function of type
DATE VMS.

So I assume something changes in your environment.  If not then I'd like to
see a script which shows this problem.  Try the settings shown above.

You can also add a CAST(.. AS DATE VMS) to change the type.

Ian

    
    Thanks for the reply.
    
    - DATE data type equates to DATE VMS
    
    Well, now it suddenly works to define the triggers as in described
    in .0. I don't know why, maybe I accidently reset the dialect or default
    (not that I can recall...), but now it works.
    
    I will try it at the customer site, so if the problem will arise again
    I'll try out your suggestions in .1.
    
    
    Thanks
    BBL