[dba-SQLServer] Question about Triggers versus Defauits

Arthur Fuller fuller.artful at gmail.com
Fri Jun 27 10:06:25 CDT 2008


The db I'm currently working on has dozens of relatively simple triggers
which all do pretty much the same thing. Here is an example:

<sql>
CREATE TRIGGER [BESTReport].[trgPersonReport_InsertUpdate]
   ON  [BESTReport].[PersonReport]
   AFTER UPDATE, INSERT
AS
BEGIN
    SET NOCOUNT ON

    INSERT INTO [Audit].[PersonReport]
        (
        [PersonReport_ID]
        ,[Person_ID]
        ,[Report_ID]
        ,[Active]
        ,[SystemUser]
        ,[SessionUser]
        ,[OriginalUser]
        ,[ActionDate]
        ,[Application]
        )
    SELECT
        [PersonReport_ID]
        ,[Person_ID]
        ,[Report_ID]
        ,[Active]
        ,SYSTEM_USER
        ,SESSION_USER
        ,original_login()
        ,getdate()
        ,app_name()
    FROM INSERTED
END
</sql>

My question is this: given that System_User, Session_User, Original_Login(),
GetDate() and App_Name() are all available at all times, then why not just
declare them as defaults on the columns rather than use a trigger? The
example cited above serves both Insert and Update, and obviously in the
latter case default columns wouldn't work, but my question remains regarding
the Insert. Is there a performance hit due to the trigger which would not be
incurred with simple defaulted columns?

TIA,
Arthur



More information about the dba-SQLServer mailing list