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;
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!!!
...
Connect with Us