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

Get day from a datetime value

   <source lang="sql">

create FUNCTION DayOnly(@Date datetime)

  RETURNS varchar(12)
  WITH SCHEMABINDING
  AS
  BEGIN
     RETURN CONVERT(varchar(12), @Date, 101)
  END
  GO

drop FUNCTION DayOnly; GO</source>

Is UPDATing a certain column

   <source lang="sql">

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</source>

The UPDATE() Function

   <source lang="sql">

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</source>