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