в 13:46, 26 мая 2010

2010-05-26T13:46:16Z

Admin: 1 версия

2010-05-26T10:24:12Z

1 версия

Новая страница

== 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>

← Предыдущая	Версия 13:46, 26 мая 2010
(нет различий)

SQL Server/T-SQL Tutorial/Trigger/Update function - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия