June 21, 2004 at 12:55 pm
Would anyone know a better method of checking if a new record is added or if any one of 4 columns in a table were changed in Table FACMDBN0 - write this info FACMDBZ1 (old & new value as well as which column):
Copy record information from inserted/deleted only if:
New Record, or update(HRJOBTITLE), update(HRJOBGRP), update(GROUPHR), update(HRMGRLVL)
********************************
The following is the only set of statements that I could get to work, but it runs too slow over the wire:
********************************
CREATE TRIGGER HRDataChanges ON FACMDBN0
FOR INSERT, UPDATE
AS
BEGIN
DECLARE @ins int, @del int, @type varchar(30), @EMPID VARCHAR(9),
@VALCHG VARCHAR(15), @VALDATE DATETIME
select @ins = count(*) from inserted
select @del = count(*) from deleted
-- count if any records exist in the inserted or deleted holding tables for N0
set @VALDATE=getdate()
-- Valdate will be used at a later time when testing is nearly complete
if @ins = 0 AND @del = 0 return
-- nothing updated/deleted
if @ins > 0 AND @del = 0 set @type = 'insert'
-- new records, employees, or contractors
else set @type = 'update'
-- employee exists, so check for existing employee id and compare data
if @type = 'update'
begin
if update(HRJOBGRP)
-- perform action for update change to the HRJOBGRP field
INSERT INTO FACMDAZ1 (EMPID, HRNAME, HRLOCCODE, COCODE,
TERMDATE, EMPSTAT, HIREDATE, HRFTPT, HRMAIL, GROUPHR, HRJOBGRP, HRCCNAME,
VALCHG, VALOLD1,VALNEW1)
SELECT isnull(del.empid," "), isnull(del.hrname," "),
isnull(del.hrloccode," "), isnull(del.cocode," "),
del.termdate, isnull(del.empstat," "),
del.hiredate, isnull(del.hrftpt," "),
isnull(del.hrmail," "), isnull(del.grouphr," "), ' ',
isnull(del.hrccname," "), 'chgJobGrp',
isnull(del.hrjobgrp," "), isnull(ins.hrjobgrp," ")
FROM deleted del inner join inserted ins on del.empid = ins.empid where del.hrjobgrp <> ins.hrjobgrp
end
begin
if update(GROUPHR)
-- perform action for update change to the GROUPHR field
INSERT INTO FACMDAZ1 (EMPID, HRNAME, HRLOCCODE, COCODE,
TERMDATE, EMPSTAT, HIREDATE, HRFTPT, HRMAIL, GROUPHR, HRJOBGRP, HRCCNAME, VALCHG, VALOLD1,VALNEW1)
SELECT isnull(del.empid," "), isnull(del.hrname," "),
isnull(del.hrloccode," "), isnull(del.cocode," "),
del.termdate, isnull(del.empstat," "),
del.hiredate, isnull(del.hrftpt," "),
isnull(del.hrmail," "), ' ',
isnull(del.hrjobgrp," "), isnull(del.hrccname," "),
'chgGroupHR', isnull(del.grouphr," "), isnull(ins.grouphr," ")
FROM deleted del inner join inserted ins on del.empid= ins.empid where del.grouphr <> ins.grouphr
end
begin
if update(HRMGRLVL)
-- perform action for update change to the HRMGRLVL field
INSERT INTO FACMDAZ1 (EMPID, HRNAME, HRLOCCODE, COCODE,
TERMDATE, EMPSTAT, HIREDATE, HRFTPT, HRMAIL, GROUPHR, HRJOBGRP, HRCCNAME, VALCHG, VALOLD1,VALNEW1)
SELECT isnull(del.empid," "), isnull(del.hrname," "),
isnull(del.hrloccode," "), isnull(del.cocode," "),
del.termdate, isnull(del.empstat," "),
del.hiredate, isnull(del.hrftpt," "),
isnull(del.hrmail," "), isnull(del.grouphr," "),
isnull(del.hrjobgrp," "), isnull(del.hrccname," "),
'chgHRMGRLVL', isnull(del.HRMGRLVL," "), isnull(ins.HRMGRLVL," ")
FROM deleted del inner join inserted ins on del.empid=ins.empid where del.HRMGRLVL <> ins.HRMGRLVL
end
begin
if update(HRJOBTITLE)
-- perform action for update change to the HRJOBTITLE field
-- was originally using COCODE for this 4th check, but BCBS wants job title changes
-- for use in reviewing if person needs new space allocation
INSERT INTO FACMDAZ1 (EMPID, HRNAME, HRLOCCODE, COCODE,
TERMDATE, EMPSTAT, HIREDATE, HRFTPT, HRMAIL, GROUPHR, HRJOBGRP, HRCCNAME, VALCHG, VALOLD1, VALNEW1)
SELECT isnull(del.empid," "), isnull(del.hrname," "),
isnull(del.hrloccode," "), isnull(del.cocode," "),
del.termdate, isnull(del.empstat," "),
del.hiredate, isnull(del.hrftpt," "),
isnull(del.hrmail," "), isnull(del.grouphr," "),
isnull(del.hrjobgrp," "), isnull(del.hrccname," "),
'chgHRJobTitle', isnull(del.hrjobtitle," "), isnull(ins.hrjobtitle," ")
FROM deleted del inner join inserted ins on del.empid=ins.empid where del.hrjobtitle <> ins.hrjobtitle
END
-- don't use ELSE here -- else
if @type = 'insert'
-- perform action if a new record is added from the HR import
-- for review of where the new person will be located
begin
INSERT INTO FACMDAZ1 (EMPID, HRNAME, HRLOCCODE, HRJOBTITLE,
COCODE, TERMDATE, EMPSTAT, HIREDATE, HRFTPT, HRMAIL, GROUPHR, HRJOBGRP, HRCCNAME, VALCHG, VALOLD1,VALNEW1)
SELECT isnull(ins.empid," "), isnull(ins.hrname," "),
isnull(ins.hrloccode," "), isnull(ins.hrjobtitle," "),
isnull(ins.cocode," "),
ins.termdate, isnull(ins.empstat," "),
ins.hiredate, isnull(ins.hrftpt," "),
isnull(ins.hrmail," "), isnull(ins.grouphr," "),
isnull(ins.hrjobgrp," "), isnull(ins.hrccname," "),
'NewRecord',' ',' '
FROM inserted ins
END
END
June 22, 2004 at 7:02 pm
What I would do is break this up into two triggers; one for update and one for insert. The insert trigger would contain the insert logic; the update trigger would contain the update logic.
Then, to check if a particular column is updated, all you have to use is "IF UPDATE(ColName) BEGIN Do Something... END
Signature is NULL
June 23, 2004 at 9:05 am
Where should I look in MS-SQL for a timeout on this trigger. My data view is giving me a 1096 minute timeout. Is that due to the trigger running an update vs an insert?
June 23, 2004 at 12:44 pm
Wow, if your trigger is timing out you've got some serious problems. I'm a little confused at to what you mean by data view. What are you using to view the data?
I won't began to question your architecture, although that's what you should be doing...all these inserts seem odd.
Suggestions:
Make EmpID your PK (clustered) if it's distinct, otherwise put a clustered index on EMPid and hrjobgrp
IF hrjobgrp is not in the PK, put an index on it (although I'm not positive this will assist, it's still a good idea)
Break them up, man! One insert trigger, one update trigger.
Something like this:
CREATE TRIGGER HRDataChanges_Update ON FACMDBN0
FOR UPDATE
AS
BEGIN
DECLARE @ins int, @del int, @type varchar(30), @EMPID VARCHAR(9),
@VALCHG VARCHAR(15), @VALDATE DATETIME
if update(HRJOBGRP)
-- perform action for update change to the HRJOBGRP field
INSERT INTO FACMDAZ1 (EMPID, HRNAME, HRLOCCODE, COCODE,
TERMDATE, EMPSTAT, HIREDATE, HRFTPT, HRMAIL, GROUPHR, HRJOBGRP, HRCCNAME,
VALCHG, VALOLD1,VALNEW1)
SELECT isnull(del.empid," "), isnull(del.hrname," "),
isnull(del.hrloccode," "), isnull(del.cocode," "),
del.termdate, isnull(del.empstat," "),
del.hiredate, isnull(del.hrftpt," "),
isnull(del.hrmail," "), isnull(del.grouphr," "), ' ',
isnull(del.hrccname," "), 'chgJobGrp',
isnull(del.hrjobgrp," "), isnull(ins.hrjobgrp," ")
FROM deleted del inner join inserted ins on del.empid = ins.empid where del.hrjobgrp <> ins.hrjobgrp
end
begin
if update(GROUPHR)
-- perform action for update change to the GROUPHR field
INSERT INTO FACMDAZ1 (EMPID, HRNAME, HRLOCCODE, COCODE,
TERMDATE, EMPSTAT, HIREDATE, HRFTPT, HRMAIL, GROUPHR, HRJOBGRP, HRCCNAME, VALCHG, VALOLD1,VALNEW1)
SELECT isnull(del.empid," "), isnull(del.hrname," "),
isnull(del.hrloccode," "), isnull(del.cocode," "),
del.termdate, isnull(del.empstat," "),
del.hiredate, isnull(del.hrftpt," "),
isnull(del.hrmail," "), ' ',
isnull(del.hrjobgrp," "), isnull(del.hrccname," "),
'chgGroupHR', isnull(del.grouphr," "), isnull(ins.grouphr," ")
FROM deleted del inner join inserted ins on del.empid= ins.empid where del.grouphr <> ins.grouphr
end
begin
if update(HRMGRLVL)
-- perform action for update change to the HRMGRLVL field
INSERT INTO FACMDAZ1 (EMPID, HRNAME, HRLOCCODE, COCODE,
TERMDATE, EMPSTAT, HIREDATE, HRFTPT, HRMAIL, GROUPHR, HRJOBGRP, HRCCNAME, VALCHG, VALOLD1,VALNEW1)
SELECT isnull(del.empid," "), isnull(del.hrname," "),
isnull(del.hrloccode," "), isnull(del.cocode," "),
del.termdate, isnull(del.empstat," "),
del.hiredate, isnull(del.hrftpt," "),
isnull(del.hrmail," "), isnull(del.grouphr," "),
isnull(del.hrjobgrp," "), isnull(del.hrccname," "),
'chgHRMGRLVL', isnull(del.HRMGRLVL," "), isnull(ins.HRMGRLVL," ")
FROM deleted del inner join inserted ins on del.empid=ins.empid where del.HRMGRLVL <> ins.HRMGRLVL
end
begin
if update(HRJOBTITLE)
-- perform action for update change to the HRJOBTITLE field
-- was originally using COCODE for this 4th check, but BCBS wants job title changes
-- for use in reviewing if person needs new space allocation
INSERT INTO FACMDAZ1 (EMPID, HRNAME, HRLOCCODE, COCODE,
TERMDATE, EMPSTAT, HIREDATE, HRFTPT, HRMAIL, GROUPHR, HRJOBGRP, HRCCNAME, VALCHG, VALOLD1, VALNEW1)
SELECT isnull(del.empid," "), isnull(del.hrname," "),
isnull(del.hrloccode," "), isnull(del.cocode," "),
del.termdate, isnull(del.empstat," "),
del.hiredate, isnull(del.hrftpt," "),
isnull(del.hrmail," "), isnull(del.grouphr," "),
isnull(del.hrjobgrp," "), isnull(del.hrccname," "),
'chgHRJobTitle', isnull(del.hrjobtitle," "), isnull(ins.hrjobtitle," ")
FROM deleted del inner join inserted ins on del.empid=ins.empid where del.hrjobtitle <> ins.hrjobtitle
END
go
CREATE TRIGGER HRDataChanges_Insert ON FACMDBN0
FOR Insert
AS
BEGIN
INSERT INTO FACMDAZ1 (EMPID, HRNAME, HRLOCCODE, HRJOBTITLE,
COCODE, TERMDATE, EMPSTAT, HIREDATE, HRFTPT, HRMAIL, GROUPHR, HRJOBGRP, HRCCNAME, VALCHG, VALOLD1,VALNEW1)
SELECT isnull(ins.empid," "), isnull(ins.hrname," "),
isnull(ins.hrloccode," "), isnull(ins.hrjobtitle," "),
isnull(ins.cocode," "),
ins.termdate, isnull(ins.empstat," "),
ins.hiredate, isnull(ins.hrftpt," "),
isnull(ins.hrmail," "), isnull(ins.grouphr," "),
isnull(ins.hrjobgrp," "), isnull(ins.hrccname," "),
'NewRecord',' ',' '
FROM inserted ins
END
Signature is NULL
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply