[thelist] SQL Variables

[Joshua Olson]

----- Original Message -----
From: "rudy" <r937 at interlog.com>
Sent: Thursday, January 23, 2003 5:43 PM


> my understanding is that a trigger is defined on the table on one or more
of
> only three table actions -- delete, insert, update -- and not on
individual
> columns

Rudy, et al,

I suppose this may depend on the database being used.  If it's SQL Server
(or perhaps other databases that support the same level of T-SQL), then you
may have a shortcut:

http://msdn.microsoft.com/library/en-us/tsqlref/ts_create2_7eeq.asp?frame=tr
ue

CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
    { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [
DELETE ] }
        [ WITH APPEND ]
        [ NOT FOR REPLICATION ]
        AS
        [ { IF UPDATE ( column )
            [ { AND | OR } UPDATE ( column ) ]
                [ ...n ]
        | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
                { comparison_operator } column_bitmask [ ...n ]
        } ]
        sql_statement [ ...n ]
    }
}

Looks like you can specify some conditionals within the trigger to see which
columns were updated.  The example from the link above:

CREATE TRIGGER updEmployeeData
ON employeeData
FOR update AS
/*Check whether columns 2, 3 or 4 has been updated. If any or all of columns
2, 3 or 4 have been changed, create an audit record. The bitmask is:
power(2,(2-1))+power(2,(3-1))+power(2,(4-1)) = 14. To check if all columns
2, 3, and 4 are updated, use = 14 in place of >0 (below).*/

   IF (COLUMNS_UPDATED() & 14) > 0
/*Use IF (COLUMNS_UPDATED() & 14) = 14 to see if all of columns 2, 3, and 4
are updated.*/
      BEGIN
-- Audit OLD record.
      INSERT INTO auditEmployeeData
         (audit_log_type,
         audit_emp_id,
         audit_emp_bankAccountNumber,
         audit_emp_salary,
         audit_emp_SSN)
         SELECT 'OLD',
            del.emp_id,
            del.emp_bankAccountNumber,
            del.emp_salary,
            del.emp_SSN
         FROM deleted del

-- Audit NEW record.
      INSERT INTO auditEmployeeData
         (audit_log_type,
         audit_emp_id,
         audit_emp_bankAccountNumber,
         audit_emp_salary,
         audit_emp_SSN)
         SELECT 'NEW',
            ins.emp_id,
            ins.emp_bankAccountNumber,
            ins.emp_salary,
            ins.emp_SSN
         FROM inserted ins
   END
GO



HTH,

-joshua