2> CREATE TABLE Activity(
3> ActivityID int IDENTITY (1, 1) NOT NULL,
4> ActivityType int NOT NULL,
5> ActivityDate datetime NOT NULL,
6> ActivityComplete bit NOT NULL
7> )
8> CREATE TABLE ActivityFootball(
9> ActivityID int NOT NULL,
10> InstantReplay bit NOT NULL,
11> FlagTackle bit NOT NULL,
12> TwoPointPlay bit NOT NULL
13> )
14> GO
1>
2> CREATE TABLE ActivitySoftball(
3> ActivityID int NOT NULL,
4> NoOfRefs tinyint NOT NULL,
5> DiamondSize tinyint NOT NULL,
6> StealingAllowed bit NOT NULL
7> )
8> GO
1>
2>
3> CREATE TRIGGER FootballIsExclusiveActivity ON ActivityFootball
4> FOR INSERT, UPDATE
5> AS
6> IF EXISTS(
7> SELECT 'True'
8> FROM Inserted i
9> LEFT JOIN Activity a
10> ON i.ActivityID = a.ActivityID
11> WHERE a.ActivityID IS NULL
12> )
13> BEGIN
14> RAISERROR('Football item Must Have Corresponding Activity',16,1)
15> ROLLBACK TRAN
16> END
17> IF EXISTS(
18> SELECT 'True'
19> FROM Inserted i
20> LEFT JOIN ActivitySoftball asb ON i.ActivityID = asb.ActivityID
21> WHERE asb.ActivityID IS NOT NULL
22> )
23> BEGIN
24> RAISERROR('Matching Softball Record Exists.',16,1)
25> ROLLBACK TRAN
26> END
27> GO
1>
2> drop TRIGGER FootballIsExclusiveActivity ;
3> GO
1>
2> drop table Activity;
3> drop table ActivityFootball;
4> drop table ActivitySoftball;
5> GO
|