The code for creating the dbo.SimpleOrders table doesn't check the order date value when inserting or updating the data. The following INSERT statement, for example, inserts an order with a pretty old and probably wrong date:

INSERT INTO dbo.SimpleOrders 
 (OrderId, OrderDate, Customer) 
VALUES 
 (3, '20100701', N'CustC'); 

You can check that the incorrect date is in the table with the following query:

SELECT o.OrderId, o.OrderDate, o.Customer 
FROM dbo.SimpleOrders AS o 
ORDER BY o.OrderId; 

Of course, it is possible to prevent inserting an order date too far in the past, or updating it to a too old value, with a check constraint. However, imagine that you don't want to just reject inserts and updates with the order date value in the past; imagine you need to correct the value to some predefined minimal value—for example, January 1, 2016. You can achieve this with a trigger.

SQL Server supports two different kinds of DML triggers and one kind of DDL trigger. DML triggers can fire after or instead of a DML action, and DDL triggers can fire only after a DDL action. For a database developer, the after DML triggers are the most useful. As you already know, you can use them for advanced constraints, for maintaining redundant data, and more. A database administrator (DBA) might use DDL triggers,  instead of DML triggers, for example, to check and reject inappropriate altering of an object, to make a view updateable. Of course, very often there is no such strict role separation in place. DDL triggers, instead of DML triggers, are not forbidden for database developers. The following code shows a trigger created on the dbo.SimpleOrders table that fires after an INSERT or an UPDATE to this table. It checks the OrderDate column value. If the date is far much in the past, it replaces it with the default minimal value:

CREATE TRIGGER trg_SimpleOrders_OrdereDate 
 ON dbo.SimpleOrders AFTER INSERT, UPDATE 
AS 
 UPDATE dbo.SimpleOrders 
    SET OrderDate = '20160101' 
 WHERE OrderDate < '20160101'; 

Let's try to insert a low order date, and update an existing value to a value too far, in the past:

INSERT INTO dbo.SimpleOrders 
 (OrderId, OrderDate, Customer) 
VALUES 
 (4, '20100701', N'CustD'); 
UPDATE dbo.SimpleOrders 
   SET OrderDate = '20110101' 
 WHERE OrderId = 3; 

You can check the data after the updates with the following query:

SELECT o.OrderId, o.OrderDate, o.Customer, 
  od.ProductId, od.Quantity 
FROM dbo.SimpleOrderDetails AS od 
  RIGHT OUTER JOIN dbo.SimpleOrders AS o 
    ON od.OrderId = o.OrderId 
ORDER BY o.OrderId, od.ProductId; 

Here is the result. As you can see, the trigger changed the incorrect dates to the predefined minimal date:

    OrderId     OrderDate   Customer  ProductId   Quantity
    ----------- ----------  -------- ----------- -----------
    1           2016-07-01  CustA     3           150
    1           2016-07-01  CustA     7           100
    2           2016-07-01  CustB     4           200
    3           2016-01-01  CustC     NULL        NULL
    4           2016-01-01  CustD     NULL        NULL
  

Note that the query used an OUTER JOIN to include the orders without the details in the result set.