Long Question follows. Thanks in advance for any assistance here.
The Oracle Database this is sourced from is 9.2 on HP-UX.
The SQL Server Database on the Target is SQL Server 2005 on Windows.
I am working on a project to convert an Oracle Database to SQL Server. A last minute requirement has been to convert a trigger over into the new database. My background is in Oracle and PL/SQL, not SQL Server and T-SQL.
Rather than post 6 pages of text with the table and trigger exactly how they appear, I'll summarize.
Let's say we have table JOB_CONTROL that is used to handle batch requests. A record is inserted to start a job, updated when the job starts processing (A process code and a job number) and then deleted when the job is done. There are other columns in the Job Control table that are also copied to the history table, but they are irrelevant here (they contain things like requestor id, the parameters used, which printer to send output to, etc). This is standard functionality in the ERP system the table resides in.
As an add on, at some stage, someone created a little batch history table to keep a log of when the job was sunmitted, started & ended.
So, JOB_CONTROL is defined as with columns: KEY, STATUS, JOBNO. (and the other misc columns)
Table HISTORY is defined with columns KEY, STATUS, JOBNO, SUBMITTED, STARTED, ENDED. (and the other misc columns)
The trigger is defined as:
CREATE OR REPLACE TRIGGER audit_batch
AFTER INSERT OR DELETE OR UPDATE ON JOB_CONTROL FOR EACH ROW
BEGIN
IF INSERTING THEN
insert HISTORY
(key, status, jobno, other_misc_columns, submitted, started, ended)
VALUES( :new.key, :new.status, :new.jobno, new:other_misc_columns, sysdate, null, null);
ELSIF UPDATING ('STATUS') THEN
if :new.status = 'E' and :old.status <> 'E'
then
update HISTORY
set STARTED = sysdate
where KEY = :old.key;
ELSIF DELETING THEN
update HISTORY set ended = sysdate
,jobno = :old.jobno
where key = :old.key;
end;
/
From the research I've done so far, I'm not sure I can get away with this in one trigger in SQL Server. I may need 3 triggers - one each for insert, update an delete. Is this correct?
Furthermore, since SQL Server seems to use an INSERTED and DELETED table to do a lot of this I'm still trying to work out exactly what I need to do.
I think my Insert trigger would look like this:
create trigger history_ins on job_control
after insert
as
begin
insert into history(KEY, STATUS, JOBNO, SUBMITTED, STARTED, ENDED)
select KEY, STATUS, JOBNO, getdate(), null, null from inserted
end
It is possible that there could be more than 1 record inserted here. If the request has large amounts of parameters that go beyond the storage capacity of the inserted record, it will update a sequence counter to 2 and then write a second record. I believe that the trigger above will copy each of these records into the history table - is this correct ?
The deleted trigger would possibly look like this:
create trigger history_del on job_control
after delete
as
begin
DECLARE @DelJob char(5)
DECLARE @DelKey char(20)
SET @DelJob = (SELECT JOBNO FROM deleted)
SET @DelKey = (SELECT KEY FROM deleted)
-- There should only be one record here, I think. If there's more, not quite sure what to do)
-- Key is actually a multi column deal , made into 1 column here for simplicity.
update history
set ended = getdate(), jobno = @DelJob
where key = @DelKey
end
Also, since KEY is a multi column field, is there a way to set about 6 different fields without declaring them all one at a time and running that 'select from deleted' query for each of them ?
Again, if there's multiple rows updated in Oracle in the same transaction, I believe this will work for both of them, as they pass one at a time through the DELETED table. Is this correct ?
The final part of this question comes to the former Oracle update trigger.
Something like this:
CREATE TRIGGER history_upd ON job_control
AFTER UPDATE
AS
--Make sure STATUS was changed
IF NOT UPDATE(STATUS)
RETURN
--Determine if new status is 'E' and old status <> 'E'
IF EXISTS (SELECT *
FROM inserted a
JOIN deleted b ON a.KEY=b.KEY
WHERE b.STATUS <> 'E' AND
a.STATUS = 'E')
BEGIN
DECLARE @UpdKey = CHAR(20)
SET @UpdKey = (SELECT a.Key
FROM inserted a
JOIN deleted b ON a.KEY=b.KEY
WHERE b.STATUS <> 'E' AND
a.STATUS = 'E')
SET @UpdJob = (SELECT a.JOBNO
FROM inserted a
JOIN deleted b ON a.KEY=b.KEY
WHERE b.STATUS <> 'E' AND
a.STATUS = 'E')
update HISTORY
set JOB_NO = @UpdJob
where KEY = @UpdKey
END
Again, KEY is a multi column field - is there a way to get all the columns in an easier way than setting variables one by one ?
Same comments apply as above on the multiple records possibly being deleted.
In summary there are 6 points that need to be addressed here.
1) the number of triggers needed : 1 like in Oracle or 3.
2) Validation / Correction of Insert Trigger
3) If Multiple records are written, are all updated sequentially in history by the trigger?
4) Validation / Correction of Delete Trigger
5) Validation / Correction of Update Trigger
6) Is there an easier way to fetch values from Inserted / Deleted other than by individually declaring / fetching them
I've not yet tested a lot of this yet - I just haven't had the time since we're very close to implementation and this was a last minute requirement. I'll be trying to test this in some detail in the morning, but wanted to get the questions out there, rather than 24 hours from now - in case I hit a wall and can't get this resolved.
If you're still with me after this novella, thank you and hope there's some SQL Trigger experts out there who can help me. Thanks in advance for your assistance.
Start Free Trial
