[dba-SQLServer] Question about Triggers versus Defauits

I haven't tested it, but I've always added things like these as default
values in columns.  I have not noticed any performance impact for default
values from built in functions


On Fri, Jun 27, 2008 at 8:06 AM, Arthur Fuller <fuller.artful at gmail.com>
wrote:

> 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
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>


-- 
-Francisco
http://sqlthis.blogspot.com | Tsql and More...