Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Can someone help convert this trigger from MS SQL Server to InterBase?


This question is answered.


Permlink Replies: 9 - Last Post: Feb 23, 2018 8:36 AM Last Post By: Jeff Overcash (...
Brian Wheatley

Posts: 60
Registered: 2/5/01
Can someone help convert this trigger from MS SQL Server to InterBase?  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 20, 2018 8:16 AM
Hello,

We are converting an internal application from MS SQL Server to InterBase and so far all is going smoothly. The only issue that we're running into is converting a trigger to InterBase SQL Syntax. If someone could assist or offer pointers it would be greatly appreciated!!!

Thank you,
Brian Wheatley

USE [FieldReportGenerator]
GO
 
/****** Object:  Trigger [dbo].[ScaleReadingCheck]    Script Date: 2/20/2018 11:13:37 AM ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
 
 
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE TRIGGER [dbo].[ScaleReadingCheck] 
   ON [dbo].[ScaleReading]
   AFTER INSERT
AS 
 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	DECLARE @Count int;
 
 
 
		SELECT @Count = COUNT(*) 
		FROM inserted
		WHERE (
	     [CellsReset] = 1
      or [CellVoltageError] = 1
      or [IdError] = 1
      or [IdErrorState] = 1
      or [NewSystem] = 1
      or [ReplacementCard] = 1
      or [ReplacementCell] = 1
      or [ScaleNotConfigured] = 1 
      or [ScaleNotLinked] = 1
      or [ScaleNotLinked_Occasionally] = 1
      or [ScaleTemeratureError] = 1
      or [ScaleVoltageError] = 1
      or [ScaleWarmup] = 1
	  )
	  and inserted.Scale != (
	  select Scale
	   from BaseEmailQueue as b
	    inner join SIHMSEmail as s
		 on b.Oid = s.Oid 
		 where cast(b.DateCreated as DATE) = cast(GETDATE() as DATE) )
 
		IF (@Count > 0)
		BEGIN
 
	DECLARE @oid uniqueidentifier = newid()
 
	insert into BaseEmailQueue(oid,SendToEmailAddress,EmailClassification,DateCreated, Sent)
	select @oid, Customer.Email, 0, GETDATE(), 0
	from Scale, Customer, Company, inserted
	where
	  inserted.Scale  = scale.Oid 
	  and Company.Oid = scale.Company
	  and Customer.oid = scale.Customer
	  
 
	insert into SIHMSEmail(oid,Company,Customer, Scale)
	 select @oid, [Scale].[Company], Scale.Customer, scale.Oid
	  from inserted, dbo.Scale, Company, Customer
	  where inserted.Scale = scale.Oid 
	  and Company.Oid = scale.Company
	  and Customer.oid = scale.Customer
	
 
	  
 
	  
 
      
	  END
END
 
GO
 
quinn wildman

Posts: 856
Registered: 12/2/99
Re: Can someone help convert this trigger from MS SQL Server to InterBase?
Correct
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 20, 2018 10:12 AM   in response to: Brian Wheatley in response to: Brian Wheatley
Below should get really close. A few useful notes:

1. Your stored procedure is using [] for Delimited Identifiers. InterBase uses "", which I believed is the SQL standard - and MSSQL can use too.
2. To return the current date, use the sql keyword current_date. Looks like MSSQL uses CAST(GETDATE() AS DATE) for the current_date.
3. To return a unique number, use generators. gen_id() with a generator gives you a new value.
4. Use declare variable for variables. When used with a SQL statement, add a colon (:) in front of them - instead of an @.
5. Statements end in a semicolon (;).
6. InterBase does not have schema's, so no dbo needed. Just make sure you have been GRANTed the appropriate permissions on the necessary tables.

--
create generator mygenerator;
create trigger "ScaleReadingCheck" for "ScaleReading" after insert as
begin
declare variable thecount integer;
declare variable oid integer;
select count(*) from inserted into :thecount where (
"CellsReset" = 1
or "CellVoltageError" = 1
or "IdError" = 1
or "IdErrorState" = 1
or "NewSystem" = 1
or "ReplacementCard" = 1
or "ReplacementCell" = 1
or "ScaleNotConfigured" = 1
or "ScaleNotLinked" = 1
or "ScaleNotLinked_Occasionally" = 1
or "ScaleTemeratureError" = 1
or "ScaleVoltageError" = 1
or "ScaleWarmup" = 1
)
and inserted.Scale <> (
select Scale
from BaseEmailQueue as b
inner join SIHMSEmail as s
on b.Oid = s.Oid
where cast(b.DateCreated as DATE) = current_date);

if (thecount>0) then
begin
oid = gen_id(mygenerator, 1);
insert into BaseEmailQueue(oid,SendToEmailAddress,EmailClassification,DateCreated, Sent)
select :oid, Customer.Email, 0, current_date, 0
from Scale, Customer, Company, inserted
where
inserted.Scale = scale.Oid
and Company.Oid = scale.Company
and Customer.oid = scale.Customer;

insert into SIHMSEmail(oid,Company,Customer, Scale)
select :oid, "Scale"."Company", Scale.Customer, scale.Oid
from inserted, Scale, Company, Customer
where inserted.Scale = scale.Oid
and Company.Oid = scale.Company
and Customer.oid = scale.Customer;
end;
end;

Brian Wheatley wrote:
Hello,

We are converting an internal application from MS SQL Server to InterBase and so far all is going smoothly. The only issue that we're running into is converting a trigger to InterBase SQL Syntax. If someone could assist or offer pointers it would be greatly appreciated!!!
...
Brian Wheatley

Posts: 60
Registered: 2/5/01
Re: Can someone help convert this trigger from MS SQL Server to InterBase?  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 21, 2018 9:58 AM   in response to: quinn wildman in response to: quinn wildman
Thank you, that is a great start!

One question though: does Firebird have an "inserted" keyword for accessing the values of the row being inserted or that was just inserted? Or would that be "NEW" in Firebird?
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: Can someone help convert this trigger from MS SQL Server to InterBase?  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 21, 2018 10:28 AM   in response to: Brian Wheatley in response to: Brian Wheatley
On 2/21/2018 12:58 PM, Brian Wheatley wrote:
Thank you, that is a great start!

One question though: does Firebird have an "inserted" keyword for accessing the values of the row being inserted or that was just inserted? Or would that be "NEW" in Firebird?

You'd need to ask the Firebird people that. InterBase and Firebird are not the
same thing anymore and haven't been for years. InterBase does not have such a
keyword.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)
Brian Wheatley

Posts: 60
Registered: 2/5/01
Re: Can someone help convert this trigger from MS SQL Server to InterBase?  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 22, 2018 5:38 AM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
InterBase doesn't have "inserted" or InterBase doesn't have "NEW" ?
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: Can someone help convert this trigger from MS SQL Server to InterBase?  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 22, 2018 9:03 AM   in response to: Brian Wheatley in response to: Brian Wheatley
On 2/22/2018 8:38 AM, Brian Wheatley wrote:
InterBase doesn't have "inserted" or InterBase doesn't have "NEW" ?

Either, InterBase does not have the ability to return values from an insert.
you have to re-query on the primary key to get things changed by a trigger.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)
Brian Wheatley

Posts: 60
Registered: 2/5/01
Re: Can someone help convert this trigger from MS SQL Server to InterBase?  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 22, 2018 1:22 PM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Then how would you handle BEFORE INSERT triggers where you want to take some action based on the values that are about to be inserted into an Interbase table?
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: Can someone help convert this trigger from MS SQL Server to InterBase?  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 22, 2018 2:02 PM   in response to: Brian Wheatley in response to: Brian Wheatley
On 2/22/2018 4:22 PM, Brian Wheatley wrote:
Then how would you handle BEFORE INSERT triggers where you want to take some action based on the values that are about to be inserted into an Interbase table?

You just do a select on your primary key you just inserted and get the final
values of the newly inserted record.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)
Brian Wheatley

Posts: 60
Registered: 2/5/01
Re: Can someone help convert this trigger from MS SQL Server to InterBase?  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 23, 2018 3:19 AM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
I'm confused.

How do I get the primary key value(s) if there is no 'inserted'?

And the BEFORE INSERT should happen before the record is written to the database... won't a SELECT return nothing?
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: Can someone help convert this trigger from MS SQL Server to InterBase?  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 23, 2018 8:36 AM   in response to: Brian Wheatley in response to: Brian Wheatley
On 2/23/2018 6:19 AM, Brian Wheatley wrote:
I'm confused.

How do I get the primary key value(s) if there is no 'inserted'?

And the BEFORE INSERT should happen before the record is written to the database... won't a SELECT return nothing?

If you need to re-look up immediately, you get the generator value and pass it
as part of the insert. Triggers that fill out a primary key should always do so
only when the passed value is NULL.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02