gistfile1.txt

DROP TABLE example;

CREATE TABLE example (
primary_id int PRIMARY KEY IDENTITY,
id int,
name varchar(20),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME,
active int
);
INSERT INTO example(id, name, active) VALUES(1, 'Phil', 1);

--DROP TRIGGER example_trigger;
CREATE TRIGGER example_trigger
ON example
INSTEAD OF UPDATE
AS 
DECLARE @id AS int;

SET @id = (SELECT id FROM inserted);

IF EXISTS(SELECT id FROM example WHERE id = @id) AND @@NESTLEVEL = 1
	
	
	
	Begin
		UPDATE example SET active = 0, updated_at = CURRENT_TIMESTAMP WHERE id = @id AND active = 1
	End

	Begin
		INSERT INTO example 
		SELECT id, name, CURRENT_TIMESTAMP AS created_at, NULL AS updated_at, 1 AS active FROM inserted
	End;

SELECT * FROM example;

UPDATE example SET name = 'Dan' WHERE id = 1 AND active = 1;

SELECT * FROM example;
DELETE FROM example;
	
--ALTER TABLE foo ADD COLUMN primary_id;
--UPDATE foo SET primary_id = id;
--ALTER TABLE foo CHANGE COLUMN id int;
--ALTER TABLE foo CHANGE COLUMN primary_id PRIMARY KEY IDENTITY;