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.
Daevin for your contribution, your answer provides the groundwork for some test mechanisms that our Developers can implement. +1
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
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.