March 4, 2004 at 8:35 am
Hello,
I'm not a programmer but looking for some insight on best practices and command syntax.
Looking for a trigger that will fire if any one of the following four fields are updates in the EMP table, also how can I fill any NULLs in the fields of EMPH with a space (' ') - an isnull() doesn't work or I have the syntax wrong?:
*********************
CREATE TRIGGER HRJobChanges
ON EMP FOR UPDATE AS
IF @@ROWCOUNT=0 RETURN
/* check if any rows available to check for updates,
if no rows affected, exit immediately */
IF ((UPDATE (grouphr)) or (UPDATE(hrjobtitle)) or (UPDATE(hrmgrlvl)) or (UPDATE(hrjobgrp)))
and (select count(*) from inserted) > 0
/* check if any one of these four fields have been changed, record both old & new for comparison, type/date/time will be added to log */
/* Audit OLD record from deleted table with alias of del and type becomes "OLD" entered to field called logtype. */
BEGIN
INSERT INTO EMPH
(logtype,empid,hrname,hrphone,
grouphr,hrjobtitle,hrjobgrp,
hrmgrlvl,empstat,cocode,
hrftpt,hiredate,hrccname,
hrloccode,hrmail,
termdate)
SELECT 'OLD',
del.empid,del.hrname,del.hrphone,
del.grouphr,del.hrjobtitle,
del.hrjobgrp,del.hrmgrlvl,del.empstat,
del.cocode,del.hrftpt,
del.hiredate,del.hrccname,
del.hrloccode,del.hrmail,
del.termdate
FROM deleted del
/* Audit NEW record from inserted table with alias of ins and type becomes "NEW" entered to field called logtype. */
INSERT INTO EMPH
(logtype,empid,hrname,hrphone,
grouphr,hrjobtitle,hrjobgrp,hrmgrlvl,
empstat,cocode,hrftpt,
hiredate,hrccname,hrloccode,
hrmail,termdate)
SELECT 'NEW',
ins.empid,ins.hrname,ins.hrphone,
ins.grouphr,ins.hrjobtitle,ins.hrjobgrp,
ins.hrmgrlvl,ins.empstat,ins.cocode,ins.hrftpt,
ins.hiredate,ins.hrccname,
ins.hrloccode,ins.hrmail,ins.termdate
FROM inserted ins
END
March 4, 2004 at 9:48 am
I don't see an ISNULL anywhere in your trigger - where are you trying to use one? The proper format is:
ISNULL(value_to_check, value_if_null)
This statement will either return value_to_check if it isn't null, or value_if_null if it is. Does that answer your question?
Ryan
March 4, 2004 at 3:09 pm
\*I had tested the following syntax on the INSERT area (see below) which resulted in compilation errors - I should have left it in as a sample but had parts of the trigger working without it. Or should the "isnull()" be on the select statement?
INSERT INTO EMPH
(logtype,isnull(empid,' '),isnull(hrname,' '),...
\* or should it be on select area of deleted/inserted *\
SELECT 'OLD',
del.empid,del.hrname,... \* see above *\
\* Also, what would anyone recommend for:*\
IF ((UPDATE (grouphr)) or (UPDATE(hrjobtitle)) or (UPDATE(hrmgrlvl)) or (UPDATE(hrjobgrp)))
\*It seems to pick up the first UPDATE() but not the subsequent column changes. Plus the trigger is returning all row inserts regardless of a change to the 4 columns.*\
March 21, 2004 at 5:21 pm
I'm trying something else to get the trigger to work if any one of the following four fields is changed:
CREATE TRIGGER HRChanges
ON EmpStdSpace
FOR UPDATE AS
IF @@ROWCOUNT=0 RETURN
/* per above if no rows affected, exit immediately */
DECLARE @ChangedColumns varbinary(8000)
SET @ChangedColumns=COLUMNS_UPDATED()
/* look at rows changed and see which column (in binary results) were changed */
/* copy record from EmpStdSpace to Z1 - logtype denotes if record is before or after update,
system date will be added to logdate through the creation of another field
according to the instructions for this trigger */
/* Audit OLD record from deleted table with alias of del and type becomes "OLD"
entered to field called logtype. */
BEGIN
INSERT INTO Z1
(logtype,
empid,
hrname,
hrphone,
grouphr,
hrjobtitle,
hrjobgrp,
hrmgrlvl,
empstat,
cocode,
hrftpt,
hiredate,
hrccname,
hrloccode,
hrmail,
termdate,
columnModified)
SELECT '--OLD',
isnull(del.empid,' '),
isnull(del.hrname,' '),
isnull(del.hrphone,' '),
isnull(del.grouphr,' '),
isnull(del.hrjobtitle,' '),
isnull(del.hrjobgrp,' '),
isnull(del.hrmgrlvl,' '),
isnull(del.empstat,' '),
isnull(del.cocode,' '),
isnull(del.hrftpt,' '),
isnull(del.hiredate,' '),
isnull(del.hrccname,' '),
isnull(del.hrloccode,' '),
isnull(del.hrmail,' '),
isnull(del.termdate,' '), chg.COLUMN_NAME
FROM deleted del
JOIN INFORMATION_SCHEMA.COLUMNS chg
ON ((chg.TABLE_NAME='EmpStdSpace') AND (@ChangedColumns & POWER (2,chg.ORDINAL_POSITION-1))<>0)
WHERE UPPER(chg.COLUMN_NAME) IN ('GROUPHR','HRJOBTITLE','HRJOBGRP','HRMGRLVL')
/* check for the specified columns being audited */
/* Audit NEW record from inserted table with alias of ins and type becomes "NEW"
entered to field called logtype. Note that -- will sort before - */
INSERT INTO Z1
(logtype,
empid,
hrname,
hrphone,
grouphr,
hrjobtitle,
hrjobgrp,
hrmgrlvl,
empstat,
cocode,
hrftpt,
hiredate,
hrccname,
hrloccode,
hrmail,
termdate,
columnModified)
SELECT '-NEW',
isnull(ins.empid,' '),
isnull(ins.hrname,' '),
isnull(ins.hrphone,' '),
isnull(ins.grouphr,' '),
isnull(ins.hrjobtitle,' '),
isnull(ins.hrjobgrp,' '),
isnull(ins.hrmgrlvl,' '),
isnull(ins.empstat,' '),
isnull(ins.cocode,' '),
isnull(ins.hrftpt,' '),
isnull(ins.hiredate,' '),
isnull(ins.hrccname,' '),
isnull(ins.hrloccode,' '),
isnull(ins.hrmail,' '),
isnull(ins.termdate,' '), chg.COLUMN_NAME
FROM inserted ins
JOIN INFORMATION_SCHEMA.COLUMNS chg
ON ((chg.TABLE_NAME='EmpStdSpace') AND (@ChangedColumns & POWER (2,chg.ORDINAL_POSITION-1))<>0)
WHERE UPPER(chg.COLUMN_NAME) IN ('GROUPHR','HRJOBTITLE','HRJOBGRP','HRMGRLVL')
/* check for the specified columns being audited */
ORDER BY ins.GROUPHR
END
This procedure seems to work in between the other procedures that are taking place when I do an import/insert test, but it doesn't return any rows in the Z1 table. Am I missing a Commit somewhere?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply