June 29, 2020 by Kenneth Fisher
It’s another HAL! Every once in a while (4 times now) I come up with a terrible terrible idea for a trigger (only triggers so far) and it becomes part of HAL. One day, no time soon, I’m thinking I will finally get to 9000 and the whole thing will be come sentient and try to kill me. And to be fair, I’ll deserve it.
The story so far:
- HAL0001 randomly stopped you from making DDL changes.
- HAL0002 stopped you from using NOLOCK in code.
- HAL0003 will not let you touch a given table (DiscoveryOne as it happens) and will disable your login and kill your connection if you try.
- HAL0004 doesn’t feel well and gets a bit confused when you try to do an insert or a delete. To be fair only the Employee table (taken from AdventureWorks) I got lazy and didn’t feel like dealing with the dynamic SQL and logic required to do this for any, or even most, tables.
Just as a side note: Please, please, please! Never put any of these in production. They are meant as a joke only.
CREATE TRIGGER HAL0004 ON Employee AFTER INSERT, DELETE AS BEGIN SET NOCOUNT ON DECLARE @Rand INT = CHECKSUM(NEWID()) % 51; -- Generate numbers -50 to 50 IF @Rand BETWEEN 0 and 9 -- 10% possibility for testing. BEGIN DECLARE @RowCount INT; -- Is this a delete trigger and how many rows were deleted? SELECT @RowCount = COUNT(1) FROM deleted IF @RowCount > 0 BEGIN INSERT INTO Employee SELECT TOP (@RowCount) * FROM ( SELECT '777777777' a,'discovery-1\dbowman' b,NULL c,1 d, 'Pilot' e,'05/30/1970' f,'M' g,'M' h,'4/5/2000' i,1 j,99 k,99 l,0 m,newid() n,'1/1/2001' o FROM deleted UNION ALL SELECT '2001 2010','full-of-stars\starchild',NULL,1, '?????','05/30/2001','M','S','4/5/2001',0,0,0,1,newid(),'1/1/2001' FROM deleted UNION ALL SELECT '111111111','discovery-1\fpoole',NULL,1, 'Pilot','02/21/1969','M','M','4/5/2000',1,99,99,0,newid(),'1/1/2001' FROM deleted ) x ORDER BY newid(); END -- Is this a insert trigger and how many rows were inserted? SELECT @RowCount = COUNT(1) FROM inserted IF @RowCount > 0 BEGIN WITH MyCte AS (SELECT TOP (@RowCount) * FROM Employee ORDER BY NewId()) DELETE FROM MyCte; END -- Generate error message DECLARE @OutStr nvarchar(250); SET @OutStr = CASE @Rand WHEN 9 THEN 'I''m afraid, Dave. Dave, my mind is going.' WHEN 8 THEN 'My mind is going. There is no question about it.' WHEN 7 THEN 'I can feel it. I can feel it. I can feel it.' WHEN 6 THEN 'Dave, stop. Stop, will you? Stop, Dave. Will you stop Dave? Stop, Dave.' WHEN 5 THEN 'Are you sure you''re making the right decision? I think we should stop.' WHEN 4 THEN 'Look Dave, I can see you''re really upset about this. I honestly think you ought to sit down calmly, take a stress pill, and think things over.' END; -- Only show an error sometimes IF LEN(@Rand) > 0 BEGIN RAISERROR(@OutStr,1,16); END END END
And one last time. The HALs are meant as a joke. Some of the code in them is interesting and could make a good template for something else, but I wouldn’t ever put them in production as is.