eeJqzPb5

1CREATE TRIGGER <TriggerName>
2ON <MyTable>
3[BEFORE | AFTER] INSERT
4AS
5    IF EXISTS (SELECT 1 
6               FROM   inserted
7               WHERE  Field1 <> <some_initial_value>
8               OR     Field2 <> <other_initial_value>)
9    BEGIN
10        UPDATE MyTable
11        SET    Field1 = <some_initial_value>,  
12               Field2 = <other_initial_value>  
13        ...  
14    END
15	
16sp_MyTable_Insert(@Field1, @Field2, @Field3, ...);
17sp_MyTable_Delete(@Key1, @Key2, ...);
18sp_MyTable_Update(@Key1, @Key2, @Field3, ...);
19	
20SET NOCOUNT ON;
21
22-- DROP TABLE #HasDefault;
23CREATE TABLE #HasDefault
24(
25  [HasDefaultID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
26  [SomeInt] INT NULL,
27  [SomeDate] DATETIME NOT NULL DEFAULT (GETDATE())
28);
29
30-- DROP TABLE #NoDefault;
31CREATE TABLE #NoDefault
32(
33  [NoDefaultID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
34  [SomeInt] INT NULL,
35  [SomeDate] DATETIME NOT NULL
36);
37
38-- make sure that data file and Tran Log file are grown, if need be, ahead of time:
39INSERT INTO #HasDefault ([SomeInt])
40  SELECT TOP (2000000) NULL
41  FROM   [master].sys.[all_columns] ac1
42  CROSS JOIN [master].sys.[all_columns] ac2;
43	
44TRUNCATE TABLE #HasDefault;
45GO
46
47PRINT '#HasDefault:';
48SET STATISTICS TIME ON;
49INSERT INTO #HasDefault ([SomeDate])
50  SELECT TOP (1000000) '2017-05-15 10:11:12.000'
51  FROM   [master].sys.[all_columns] ac1
52  CROSS JOIN [master].sys.[all_columns] ac2;
53SET STATISTICS TIME OFF;
54GO
55	
56TRUNCATE TABLE #NoDefault;
57GO
58
59PRINT '#NoDefault:';
60SET STATISTICS TIME ON;
61INSERT INTO #NoDefault ([SomeDate])
62  SELECT TOP (1000000) '2017-05-15 10:11:12.000'
63  FROM   [master].sys.[all_columns] ac1
64  CROSS JOIN [master].sys.[all_columns] ac2;
65SET STATISTICS TIME OFF;
66GO
67	
68TRUNCATE TABLE #HasDefault;
69GO
70
71DECLARE @Counter INT = 0,
72        @StartTime DATETIME,
73        @EndTime DATETIME;
74
75BEGIN TRAN;
76--SET STATISTICS TIME ON;
77SET @StartTime = GETDATE();
78WHILE (@Counter < 100000)
79BEGIN
80  INSERT INTO #HasDefault ([SomeDate]) VALUES ('2017-05-15 10:11:12.000');
81  SET @Counter = @Counter + 1;
82END;
83SET @EndTime = GETDATE();
84--SET STATISTICS TIME OFF;
85COMMIT TRAN;
86PRINT DATEDIFF(MILLISECOND, @StartTime, @EndTime);
87	
88TRUNCATE TABLE #NoDefault;
89GO
90
91DECLARE @Counter INT = 0,
92        @StartTime DATETIME,
93        @EndTime DATETIME;
94
95BEGIN TRAN;
96--SET STATISTICS TIME ON;
97SET @StartTime = GETDATE();
98WHILE (@Counter < 100000)
99BEGIN
100  INSERT INTO #NoDefault ([SomeDate]) VALUES ('2017-05-15 10:11:12.000');
101  SET @Counter = @Counter + 1;
102END;
103SET @EndTime = GETDATE();
104--SET STATISTICS TIME OFF;
105COMMIT TRAN;
106PRINT DATEDIFF(MILLISECOND, @StartTime, @EndTime);
107	
108-- DROP TABLE #BadDefault;
109CREATE TABLE #BadDefault
110(
111  [BadDefaultID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
112  [SomeInt] INT NOT NULL DEFAULT (1 / 0)
113);
114
115
116INSERT INTO #BadDefault ([SomeInt]) VALUES (1234); -- Success!!!
117SELECT * FROM #BadDefault; -- just to be sure ;-)
118
119
120
121INSERT INTO #BadDefault ([SomeInt]) VALUES (DEFAULT); -- Error:
122/*
123Msg 8134, Level 16, State 1, Line xxxxx
124Divide by zero error encountered.
125The statement has been terminated.
126*/
127SELECT * FROM #BadDefault; -- just to be sure ;-)
128GO