SQL Server/T-SQL Tutorial/Trigger/Update function

Get day from a datetime value

create FUNCTION DayOnly(@Date datetime)
   RETURNS varchar(12)
   WITH SCHEMABINDING
   AS
   BEGIN
      RETURN CONVERT(varchar(12), @Date, 101)
   END
   GO
drop FUNCTION DayOnly;
GO

Is UPDATing a certain column

2>
3>
4> CREATE TABLE titles(
5>    title_id       varchar(20),
6>    title          varchar(80)       NOT NULL,
7>    type           char(12)          NOT NULL,
8>    pub_id         char(4)               NULL,
9>    price          money                 NULL,
10>    advance        money                 NULL,
11>    royalty        int                   NULL,
12>    ytd_sales      int                   NULL,
13>    notes          varchar(200)          NULL,
14>    pubdate        datetime          NOT NULL
15> )
16> GO
1>
2> insert titles values ("1", "Secrets",   "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94")
3> insert titles values ("2", "The",       "business",     "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91")
4> insert titles values ("3", "Emotional", "psychology",   "0736", $7.99,  $4000.00, 10, 3336,"Note 3","06/12/91")
5> insert titles values ("4", "Prolonged", "psychology",   "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91")
6> insert titles values ("5", "With",      "business",     "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91")
7> insert titles values ("6", "Valley",    "mod_cook",     "0877", $19.99, $0.00,    12, 2032,"Note 6","06/09/91")
8> insert titles values ("7", "Any?",      "trad_cook",    "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91")
9> insert titles values ("8", "Fifty",     "trad_cook",    "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91")
10> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1> CREATE TABLE sales(
2>    stor_id        char(4)           NOT NULL,
3>    ord_num        varchar(20)       NOT NULL,
4>    ord_date       datetime          NOT NULL,
5>    qty            smallint          NOT NULL,
6>    payterms       varchar(12)       NOT NULL,
7>    title_id       varchar(80)
8> )
9> GO
1> insert sales values("1", "QA7442.3", "09/13/94", 75, "ON Billing","1")
2> insert sales values("2", "D4482",    "09/14/94", 10, "Net 60",    "1")
3> insert sales values("3", "N914008",  "09/14/94", 20, "Net 30",    "2")
4> insert sales values("4", "N914014",  "09/14/94", 25, "Net 30",    "3")
5> insert sales values("5", "423LL922", "09/14/94", 15, "ON Billing","3")
6> insert sales values("6", "423LL930", "09/14/94", 10, "ON Billing","2")
7> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> CREATE TABLE stores(
4>    stor_id        char(4)           NOT NULL,
5>    stor_name      varchar(40)           NULL,
6>    stor_address   varchar(40)           NULL,
7>    city           varchar(20)           NULL,
8>    state          char(2)               NULL,
9>    zip            char(5)               NULL
10> )
11> GO
1> insert stores values("1","B","567 Ave.","Tustin",   "CA","92789")
2> insert stores values("2","N","577 St.", "Los Gatos","CA","96745")
3> insert stores values("3","T","679 St.", "Portland", "OR","89076")
4> insert stores values("4","F","89  St.", "Fremont",  "CA","90019")
5> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>     ALTER TABLE sales
3>     ADD title VARCHAR(80) NULL
4>     GO
1>
2>     CREATE TRIGGER myTrigger ON titles
3>     FOR UPDATE
4>     AS
5>     DECLARE @intRowCount int
6>     SELECT @intRowCount = @@RowCount
7>     IF @intRowCount > 0
8>         BEGIN
9>             IF UPDATE(title)
10>                 BEGIN
11>                     UPDATE sales SET sales.title = inserted.title
12>                         FROM inserted INNER JOIN sales
13>                         ON inserted.title_id = sales.title_id
14>                 END
15>         END
16>     GO
1>
2>
3>     drop TRIGGER myTrigger;
4>     drop table sales;
5>     drop table titles;
6>     drop table stores;
7>     GO

The UPDATE() Function

5>
6> CREATE TABLE Products (
7>      ProductID int NOT NULL ,
8>      ProductName nvarchar (40) NOT NULL ,
9>      SupplierID int NULL ,
10>     CategoryID int NULL ,
11>     QuantityPerUnit nvarchar (20) NULL ,
12>     UnitPrice money NULL,
13>     UnitsInStock smallint NULL,
14>     UnitsOnOrder smallint NULL,
15>     ReorderLevel smallint NULL,
16>     Discontinued bit NOT NULL
17> )
18> GO
1> INSERT Products VALUES(1,"F",15,4,"10 - 999 g pkgs.",61.5,66,6,6,6)
2> INSERT Products VALUES(2,"M",14,4,"24 - 888 g pkgs.",34.8,74,7,7,7)
3> INSERT Products VALUES(3,"R",17,8,"24 - 777 g jars",17,171,0,5,0)
4> INSERT Products VALUES(4,"L",4,7,"5 kg pkg.",10,4,20,5,0)
5> INSERT Products VALUES(5,"R",12,1,"24 - 0.5 l bottles",1.23,445,0,25,0)
6> INSERT Products VALUES(6,"L",23,1,"500 ml",18,57,1,20,0)
7> INSERT Products VALUES(7,"O",12,2,"12 boxes",13,23,0,15,0)
8> go
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>    create TRIGGER ProductIsRationed
3>       ON Products
4>       FOR UPDATE
5>    AS
6>       IF UPDATE(UnitsInStock)
7>       BEGIN
8>       IF EXISTS
9>          (
10>           SELECT "True"
11>           FROM Inserted i
12>           JOIN Deleted d
13>              ON i.ProductID = d.ProductID
14>           WHERE (d.UnitsInStock - i.UnitsInStock) > d.UnitsInStock / 2
15>              AND d.UnitsInStock - i.UnitsInStock > 0
16>          )
17>       BEGIN
18>          RAISERROR("Cannot reduce stock by more than 50%% at once.",16,1)
19>          ROLLBACK TRAN
20>       END
21>       END
22> GO
1>
2> drop TRIGGER ProductIsRationed;
3> GO
1>
2> drop table Products;
3> GO