kp0zCWsC

1-- 3.
2CREATE TRIGGER PropertyUnavailable
3	BEFORE INSERT ON Contracts
4	FOR EACH ROW
5	WHEN (EXISTS (SELECT *
6		      FROM Contracts
7		      WHERE Inserted.dateIn < Contracts.dateOut))
8	UPDATE Inserted
9	SET Inserted.propertyId = NULL;
10
11-- 4.
12DROP TABLE IF EXISTS UnreasonableRaise;
13CREATE TABLE UnreasonableRaise (
14	propertyId INTEGER REFERENCES Properties(propertyId),
15	ownerTaxId INTEGER REFERENCES Owners(ownerTaxId),
16	clientTaxId INTEGER REFERENCES Clients(clientTaxId),
17	dateIn DATE,
18	dateOut DATE,
19	rent REAL NOT NULL,
20	raise REAL NOT NULL,
21    	PRIMARY KEY(propertyId, ownerTaxId, clientTaxId, dateIn));
22
23CREATE TRIGGER LogUnreasonableRaise
24	AFTER UPDATE ON Contracts
25	FOR EACH ROW
26	WHEN (NEW.rent / OLD.rent > 1.1)
27	INSERT INTO UnreasonableRaise VALUES(NEW.propertyId, 
28					     NEW.ownerTaxId, 
29					     NEW.clientTaxId, 
30					     NEW.dateIn, 
31					     NEW.dateOut, 
32					     NEW.rent, 
33					     NEW.rent/OLD.rent);
34
35-- 5.
36CREATE TRIGGER AdBudget
37	BEFORE INSERT ON Advertisements
38	FOR EACH ROW
39	WHEN (SUM((SELECT cost
40	      	   FROM Advertisements
41	      	   WHERE EXTRACT(MONTH FROM Advertisements.date) = EXTRACT(MONTH FROM NOW()).cost) +
42	      SUM ((SELECT cost
43	      	    FROM Inserted
44	      	    WHERE EXTRACT(MONTH FROM Inserted.date) = EXTRACT(MONTH FROM NOW()).cost) > 5000)
45	UPDATE Inserted
46	SET Inserted.propertyID = NULL;
47
48-- 6.
49CREATE TRIGGER AdBudget
50	BEFORE INSERT ON Advertisements
51	FOR EACH ROW
52	WHEN (SUM((SELECT cost
53	      	   FROM Advertisements
54	      	   WHERE EXTRACT(MONTH FROM Advertisements.date) = EXTRACT(MONTH FROM NOW()).cost) +
55	      SUM ((SELECT cost
56	      	    FROM Inserted
57	      	    WHERE EXTRACT(MONTH FROM Inserted.date) = EXTRACT(MONTH FROM NOW()).cost) > 5000)
58	UPDATE Inserted
59	SET Inserted.mediumName ='Immobilien Scout24';