Sql-server – Is SELECT * ok in a Trigger. Or am I asking for trouble

sql servert-sql

I'm caught in a debate at work, and I need some advice on possible Pitfalls I could be overlooking.

Imagine a scenario where a Trigger is used to copy Deleted Records to an Audit Table.
The Trigger uses SELECT *. Everyone points and shouts and tells us how bad this is.

However, if a modification is made to the Structure of the Main Table, and the Audit Table is overlooked then the Trigger will generate an error letting people know the Audit table also needs modification.

The error will be caught during testing on our DEV servers. But we need to ensure Production Matches DEV, so we allow SELECT * in Production Systems (Triggers only).

So my Question is: I'm being pushed to remove the SELECT *, but I'm not sure how else to ensure we are automatically capturing Development Errors of this nature, any ideas or is this best practice?

I've put together an example below:

--Create Test Table
CREATE TABLE dbo.Test(ID INT IDENTITY(1,1), Person VARCHAR(255))
--Create Test Audit Table
CREATE TABLE dbo.TestAudit(AuditID INT IDENTITY(1,1),ID INT, Person VARCHAR(255))

--Create Trigger on Test
CREATE TRIGGER [dbo].[trTestDelete] ON [dbo].[Test] AFTER DELETE
NOT FOR REPLICATION
AS
BEGIN
    SET NOCOUNT ON;
    INSERT  dbo.TestAudit([ID], [Person])
    SELECT  *
    FROM    deleted
END

--Insert Test Data into Test
INSERT INTO dbo.Test VALUES
('Scooby')
,('Fred')
,('Shaggy')

--Perform a delete
DELETE dbo.Test WHERE Person = 'Scooby'

UPDATE (rephrase question):

I'm a DBA and need to ensure Developers don't provide poorly thought-out Deployment scripts by contributing to our Best Practice Documentation.
SELECT * causes an error in DEV when the Developer overlooks the Audit Table (this is a safety net) so the error is caught early in the development process. But somewhere in the SQL Constitution – 2nd amendment it reads "Thou shalt not use SELECT *" . So now there is a push to get rid of the Safety Net.

How would you replace the Safety Net, or should I consider this to be best practice for Triggers?

UPDATE 2: (solution)

Thankyou for all of your input, I'm not sure if I have a clear answer because this appears to be a very Grey subject. But collectively you've provided discussion points that can help our developers move forward with defining their Best Practice.

Thanks Daevin for your contribution, your answer provides the groundwork for some test mechanisms that our Developers can implement. +1

Thanks CM_Dayton, your suggestions contributing to best practice can be beneficial to anyone who is devloping Audit Triggers. +1

Big Thanks to ypercube, you've raised plenty of thought around the issues concerning tables undergoing different forms of definition changes. +1

In conclusion:

Is Select * ok in a tigger? Yes, it's a Grey area, don't blindly follow the "Select * is Bad" ideology".

Am I asking for Trouble? Yes, we do more than just add new columns to tables.

Best Answer

Typically, it is considered "lazy" programming.

Given that you're specifically inserting two values into your TestAudit table here, I'd be careful to make sure your select is also getting exactly two values. Because if, for some reason, that Test table has or ever gets a third column, this trigger will fail.

Not directly related to your question but if you're setting up an audit table, I'd also add some additional columns to your TestAudit table to...

  • track the action you are auditing (delete in this case, vs inserts or updates)
  • date/time column to track when the audit event occurred
  • user ID column to track who carried out the action you are auditing.

So that results in a query like:

INSERT dbo.TestAudit([ID], [Person], [AuditAction], [ChangedOn], [ChangedBy])
SELECT [ID], [Person], 
   'Delete', -- or a 'D' or a numeric lookup to an audit actions table...
   GetDate(), -- or SYSDATETIME() for greater precision
   SYSTEM_USER -- or some other value for WHO made the deletion
FROM deleted

That way you're getting the exact columns you need and you're auditing what/when/why/who the audit event is about.

Related Question