HAL0004 – I don’t feel well Dave

Leave a comment

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,136 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: