uGMww4h5

1IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'UPGRADE_HISTORY')
2DROP TABLE UPGRADE_HISTORY
3
4IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'TEST_CODE_TABLE')
5    DROP TABLE TEST_CODE_TABLE
6
7CREATE TABLE UPGRADE_HISTORY (
8    UPDATE_DATE     DATE            NOT NULL,
9    VERSION_TXT     VARCHAR(50)     NOT NULL,
10    PRIMARY KEY     (UPDATE_DATE, VERSION_TXT)
11)
12
13CREATE TABLE TEST_CODE_TABLE (
14    CODE_VALUE      INT             PRIMARY KEY,
15    DESCRIPTION_TXT VARCHAR(250)    NOT NULL        
16)
17
18INSERT INTO UPGRADE_HISTORY VALUES
19(DATEADD(d, -3, GETDATE()), '6.2.5000'),
20(DATEADD(d, -1, GETDATE()), '6.2.5001'),
21(DATEADD(d, -1, GETDATE()), '6.2.5002'),
22(DATEADD(d, -1, GETDATE()), '6.3.6000.0'),
23(DATEADD(d, -1, GETDATE()), '6.3.6001.0')
24
25INSERT INTO TEST_CODE_TABLE VALUES
26(1001, 'Test Code Table'),
27(1002, 'Test Code Table 2')
28	
29INSERT INTO UPGRADE_HISTORY VALUES
30(GETDATE(), '6.3.6001.0')
31
32GO
33
34PRINT 'Test Code Table Change'
35GO
36
37UPDATE TEST_CODE_TABLE SET DESCRIPTION_TXT = 'Test Code Table Change' WHERE CODE_VALUE = 1002;
38GO
39	
40INSERT INTO UPGRADE_HISTORY VALUES
41(GETDATE(), '6.3.6003.0')
42
43GO
44
45PRINT 'Test Error'
46GO
47
48INSERT INTO CODE_TABLE VALUES (1001, 'Test') 
49
50--This will throw an error since this will conflict with the primary key
51--of the code table (or you know, because I just noticed it doesn't call
52--the right table, it's really relevant since I want it to throw an  
53--error, w/e what it is)
54
55GO
56
57PRINT 'Second Test Code Table Change'
58GO
59
60UPDATE TEST_CODE_TABLE SET DESCRIPTION_TXT = 'Test Code Table Change 2' WHERE CODE_VALUE = 1002;
61
62--We still want this to execute.    
63
64GO
65	
66Test Code Table Change
67Test Error
68Second Test Code Table Change