HAL0004 – I don’t feel well Dave

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-1dbowman' 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' oFROM deleted UNION ALL
SELECT '2001 2010','full-of-starsstarchild',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-1fpoole',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.