Converting SQL Server Trigger/Procedure (Long)

I'm relatively new to the Informix realm, and I'm having trouble trying to convert a SQL Server Trigger over to Informix. I've checked the books as best I can, and scanned the newsgroups, but I haven't found what I need. I was wondering if someone can give me a hint, or point me in the right direction. Here's the SQL Server Trigger GO CREATE TRIGGER PDD_A ON AFOR DELETE AS BEGIN DECLARE @varIsPDUser integer DECLARE @varSiteID integer EXECUTE PDIsPDUser @varIsPDUser OUTPUT IF @varIsPDUser <> 1 BEGIN EXECUTE PDGetSiteID @varSiteID OUTPUT UPDATE PDCA SET PDCDel = 'D', PDC1 = GETDATE(), PDC2 = @varSiteID, PDC1F1 = GETDATE(), PDC2F1 = @varSiteID, PDC1F2 = GETDATE(), PDC2F2 = @varSiteID WHERE EXISTS( SELECT AID FROM deleted d WHERE d.AID = PDCA.CAID ) END END GO I started to convert the syntax over to the Informix style and realised that I can't do all this inside a trigger. So I set about creating a procedure and have the trigger call the procedure. But then I realised the SQL Server code was executing an update against the deleted table. Obviously I can't pass that parameter into a procedure. And I don't think I can perform all I need inside a trigger -- so I'm a little stuck. Here's the re-vamped trigger code that I created, but can't run inside the trigger. Note that PDIsPDUser() is a stored procedure. And I haven't converted the GETDATE() function over yet. CREATE PROCEDURE delete_on_a( old_AID INT ) DEFINE isPDUser INT; LET isPDUSER = PDIsPDUser(); IF isPDUser <> 1 BEGIN DEFINE siteID INT; LET siteID = PDGetSiteID(); UPDATE PDCA SET PDCDel = 'D', PDC1 = GETDATE(), PDC2 = siteID, PDC1F1 = GETDATE(), PDC2F1 = siteID, PDC1F2 = GETDATE(), PDC2F2 = siteID, WHERE EXISTS( SELECT AID FROM deleted d WHERE old_AID = PDCA.CAID ); END ) END PROCEDURE; Anyone able to give me a hint? Thanks, Cheers, Mark ** remove the ".useless" from the above address if sending email **

Mark, I have extensive experience in converting SQLSERVER stored procedures and triggers to Informix. I can't give you all the answers, but this should help: 1) purchase INFORMIX STORED PROCEDURE PROGRAMMING by Michael Gonzales through Informix Press. Explains everything and has examples. 2) Purchase or Download (used to be free) Informix Relational Object Manager. This utility has a nice Stored Procedure/Trigger Editor for Informix. 3) There are some things that Informix stored procedures can't do that SQLSERVER can. Unless you are running Universal Server, then anything is possible. After looking at your code, please purchase the book and it will explain (and give examples) of Informix Triggers and Stored Procedures. Good Luck! David Meyer Guerilla Software Mark Cheers wrote in message <36a70dc0.594579750@nr1.toronto.istar.net>... > >I'm relatively new to the Informix realm, and I'm having trouble >trying to convert a SQL Server Trigger over to Informix. I've checked >the books as best I can, and scanned the newsgroups, but I haven't >found what I need. I was wondering if someone can give me a hint, or >point me in the right direction. > >Here's the SQL Server Trigger > >GO >CREATE TRIGGER PDD_A >ON A>FOR DELETE AS > BEGIN > DECLARE @varIsPDUser integer > DECLARE @varSiteID integer > > EXECUTE PDIsPDUser @varIsPDUser OUTPUT > IF @varIsPDUser <> 1 > BEGIN > EXECUTE PDGetSiteID @varSiteID OUTPUT > > UPDATE PDCA > SET PDCDel = 'D', > PDC1 = GETDATE(), > PDC2 = @varSiteID, > PDC1F1 = GETDATE(), > PDC2F1 = @varSiteID, > PDC1F2 = GETDATE(), > PDC2F2 = @varSiteID > WHERE EXISTS( SELECT AID > FROM deleted d > WHERE d.AID = PDCA.CAID ) > END > END >GO > >I started to convert the syntax over to the Informix style and >realised that I can't do all this inside a trigger. So I set about >creating a procedure and have the trigger call the procedure. But >then I realised the SQL Server code was executing an update against >the deleted table. Obviously I can't pass that parameter into a >procedure. And I don't think I can perform all I need inside a >trigger -- so I'm a little stuck. > >Here's the re-vamped trigger code that I created, but can't run inside >the trigger. Note that PDIsPDUser() is a stored procedure. And I >haven't converted the GETDATE() function over yet. > >CREATE PROCEDURE delete_on_a( old_AID INT )> >DEFINE isPDUser INT; >LET isPDUSER = PDIsPDUser(); > >IF isPDUser <> 1 > BEGIN > DEFINE siteID INT; > LET siteID = PDGetSiteID(); > > UPDATE PDCA > SET PDCDel = 'D', > PDC1 = GETDATE(), > PDC2 = siteID, > PDC1F1 = GETDATE(), > PDC2F1 = siteID, > PDC1F2 = GETDATE(), > PDC2F2 = siteID, > WHERE EXISTS( SELECT AID > FROM deleted d > WHERE old_AID = PDCA.CAID ); > > END >) >END PROCEDURE; > >Anyone able to give me a hint? > >Thanks, > > >Cheers, >Mark > >** remove the ".useless" from the above address if sending email **

In article <36a70dc0.594579750@nr1.toronto.istar.net>, Mark Cheers <markc.useless@cntc.com> writes > >I'm relatively new to the Informix realm, and I'm having trouble >trying to convert a SQL Server Trigger over to Informix. I've checked >the books as best I can, and scanned the newsgroups, but I haven't >found what I need. I was wondering if someone can give me a hint, or >point me in the right direction. > Check the FAQ at www.smooth1.demon.co.uk Section 4.8.6 How do I convert Sybase Stored Procedures to Informix? >Here's the SQL Server Trigger > >GO >CREATE TRIGGER PDD_A >ON A>FOR DELETE AS > BEGIN > DECLARE @varIsPDUser integer > DECLARE @varSiteID integer > > EXECUTE PDIsPDUser @varIsPDUser OUTPUT > IF @varIsPDUser <> 1 > BEGIN > EXECUTE PDGetSiteID @varSiteID OUTPUT > > UPDATE PDCA > SET PDCDel = 'D', > PDC1 = GETDATE(), > PDC2 = @varSiteID, > PDC1F1 = GETDATE(), > PDC2F1 = @varSiteID, > PDC1F2 = GETDATE(), > PDC2F2 = @varSiteID > WHERE EXISTS( SELECT AID > FROM deleted d > WHERE d.AID = PDCA.CAID ) > END > END >GO > >I started to convert the syntax over to the Informix style and >realised that I can't do all this inside a trigger. So I set about >creating a procedure and have the trigger call the procedure. But >then I realised the SQL Server code was executing an update against >the deleted table. Obviously I can't pass that parameter into a >procedure. And I don't think I can perform all I need inside a >trigger -- so I'm a little stuck. > >Here's the re-vamped trigger code that I created, but can't run inside >the trigger. Note that PDIsPDUser() is a stored procedure. And I >haven't converted the GETDATE() function over yet. > >CREATE PROCEDURE delete_on_a( old_AID INT )> >DEFINE isPDUser INT; >LET isPDUSER = PDIsPDUser(); > >IF isPDUser <> 1 > BEGIN > DEFINE siteID INT; > LET siteID = PDGetSiteID(); > > UPDATE PDCA > SET PDCDel = 'D', > PDC1 = GETDATE(), > PDC2 = siteID, > PDC1F1 = GETDATE(), > PDC2F1 = siteID, > PDC1F2 = GETDATE(), > PDC2F2 = siteID, > WHERE EXISTS( SELECT AID > FROM deleted d > WHERE old_AID = PDCA.CAID ); > > END >) >END PROCEDURE; > >Anyone able to give me a hint? > >Thanks, > > >Cheers, >Mark > >** remove the ".useless" from the above address if sending email ** -- David Williams