A weird feature in SQL Server is the INSTEAD OF trigger. With this trigger you can divert the database to do something other than what the user/application wanted. You can have INSTEAD OF triggers for insert/update/delete.
Where would this be useful? I’ve had application that have to be changed, but the source code is no longer around (had a client with this problem). The business wanted change what data was written to the database, but they could change the application. So I added an INSTEAT OF trigger to check the data before it was inserted/updated and have the corrected values inserted/updated instead.
Here is a real simple example. A test table is created and loaded with one record. An insert INSTEAD OF trigger is added. This trigger just returns, so the insert never happens. No matter what INSERT statement the user/application tries it just gets ignored.
CREATE TABLE Test ( TestID int IDENTITY(1,1) NOT NULL , Name varchar(64) NULL , CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED ([TestID] ASC) ) GO INSERT INTO Test (Name) VALUES ('Hello World') GO SELECT * FROM dbo.Test GO CREATE TRIGGER tiTest ON Test INSTEAD OF INSERT AS BEGIN RETURN END GO INSERT INTO Test (Name) VALUES ('Good Bye World') GO SELECT * FROM dbo.Test GO
So you can see here the before and after of the Insert statement. It just gets ignored.