Trigger & 11.50 & HOSTNAME

Posted in 2008

Topics: Server Administration, Triggers, Constraints & Referential Integrity, Networking & sqlhosts Configuration

HANNES BROTTRAGER — Fri, 20 Jun 2008 07:12:44 — source: IIUG Forums & Mailing Lists

I would like to create a trigger that use the HOSTNAME of the client instead of DBSERVERNAME. create trigger "pps1".up_adrmatch update of adrmatch on "pps1".adr referencing old as deleted new as inserted for each row ( insert into "pps1".stprot (protdatum,protzeit,userid,tabellenname,spaltenname,kz_dbaktion,mandnr,keyteil1, keyteil2,keyteil3,keyteil4,keyteil5,keyteil6,keyteil7,keyteil8,wert_alt,wert_neu ) select distinct ((CURRENT year to year || CURRENT month to month ) || CURRENTday to day ) ,((CURRENT hour to hour || CURRENT minute to minute ) || CURRENT second to second ) ,( USER || DBSERVERNAME ) ,'adr' ,'adrmatch' ,'U' ,0 ,inserted.inr ,'adrmatch' ,'' ,'' ,'' ,'' ,'' ,'' ,deleted.adrmatch ,inserted.adrmatch from "pps1".sttrg x0 where ((inserted.adrmatch != deleted.adrmatch ) OR (((inserted.adrmatch IS NULL ) OR (deleted.adrmatch IS NULL ) ) AND ((inserted.adrmatch IS NOT NULL ) OR (deleted.adrmatch IS NOT NULL ) ) ) ) ); Any ideas? Thanks

↪ replying to HANNES BROTTRAGER

Karl Ostner — Fri, 20 Jun 2008 07:33:37 — source: IIUG Forums & Mailing Lists

select hostname from sysmaster:syssessions where sid=dbinfo('sessionid'); HTH, Karl Ostner IBM Deutschland Entwicklung GmbH Chairman of the Supervisory Board: Martin Jetter Board of Management: Herbert Kircher Corporate Seat: Boeblingen, Germany Reg.-Gericht: Amtsgericht Stuttgart, HRB 243294 "HANNES BROTTRAGER" <brottrager@stift To admont.at> ids@iiug.org Sent by: cc ids-bounces@iiug. org Subject Trigger & 11.50 & HOSTNAME [12474] 20.06.2008 09:12 Please respond to ids@iiug.org I would like to create a trigger that use the HOSTNAME of the client instead of DBSERVERNAME. create trigger "pps1".up_adrmatch update of adrmatch on "pps1".adr referencing old as deleted new as inserted for each row ( insert into "pps1".stprot (protdatum,protzeit,userid,tabellenname,spaltenname,kz_dbaktion,mandnr,keyteil1, keyteil2,keyteil3,keyteil4,keyteil5,keyteil6,keyteil7,keyteil8,wert_alt,wert_neu ) select distinct ((CURRENT year to year || CURRENT month to month ) ||CURRENT day to day ) ,((CURRENT hour to hour || CURRENT minute to minute ) || CURRENT second to second ) ,( USER || DBSERVERNAME ) ,'adr' ,'adrmatch' ,'U' ,0 ,inserted.inr ,'adrmatch' ,'' ,'' ,'' ,'' ,'' ,'' ,deleted.adrmatch ,inserted.adrmatch from "pps1".sttrg x0 where ((inserted.adrmatch != deleted.adrmatch ) OR (((inserted.adrmatch IS NULL ) OR (deleted.adrmatch IS NULL ) ) AND ((inserted.adrmatch IS NOT NULL ) OR (deleted.adrmatch IS NOT NULL ) ) ) ) ); Any ideas? Thanks ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.