I am attempting to create a trigger for if a row is inserted and if a row is deleted. If a row is inserted a print statement will give one message, and if a row is deleted a print statement will give another message. My teacher used this example, but with DDL statements instead of the DML statements I put in. My question is how I can obtain the equivalent, but making it so DML statements can work? So far my output says the else print statement whether a row is inserted or deleted. I don't beleive it even counts the insert statement at all.
Here is my schema.
CREATE TABLE dbo.Customers
(
CustomerID INT NOT NULL IDENTITY PRIMARY KEY,
CustomerFName VARCHAR(30) NOT NULL,
CustomerLName VARCHAR(30) NOT NULL,
CustomerAddress1 VARCHAR(50) NOT NULL,
CustomerAddress2 VARCHAR(50) NULL,
CustomerCity VARCHAR(50) NOT NULL,
CustomerState CHAR(2) NOT NULL,
CustomerZipCode INT NOT NULL,
CustomerHome VARCHAR(20) NOT NULL,
CustomerCell VARCHAR(20) NOT NULL,
CustomerEmail VARCHAR(50) NULL
);
This was my data I already had.
CustomerID CustomerFName CustomerLName CustomerAddress1 CustomerAddress2 CustomerCity CustomerState CustomerZipCode CustomerHome CustomerCell CustomerEmail
1 Jane Swanson 123 Negley Ave Apartment #3 Pittsburgh PA 15222 (412) 555 - 6678 (412) 555 - 6789 [email protected]
2 Phillip Connely 167 Warble St NULL Pittsburgh PA 15212 (412) 555 - 9463 (412) 555 - 6797 [email protected]
3 Alfred Mansley 41 Wild Place Apartment #4 Pittsburgh PA 15205 (412) 555 - 9371 (412) 555 - 4259 [email protected]
4 Angel Smith 2050 Morningside Ave NULL Pittsburgh PA 15222 (412) 555 - 6931 (412) 555 - 1135 [email protected]
5 Walter Weezley 2670 Butler St NULL Pittsburgh PA 15210 (412) 567 - 6931 (412) 666 - 1256 [email protected]
Trigger creation
CREATE TRIGGER Table_Update
ON Customers
AFTER INSERT, DELETE
AS
If EXISTS (SELECT 1 FROM inserted)
PRINT 'The company has gained customers.'
ELSE
PRINT 'The company has lost customers.'
Test
INSERT INTO Customers
VALUES('Pter', 'Jackson', '1240 Wlnut St', 'Apartment #1', 'Pittsburgh',
'PA', 15232, '(724) 789 - 1234', '(724) 555 - 8706', '[email protected]');
DELETE Customers
WHERE CustomerFName = 'Pter';
Upon inserting a row I get the message:
The company has gained customers.
Msg 2601, Level 14, State 1, Procedure Table_Update, Line 15
Cannot insert duplicate key row in object 'dbo.Customers' with unique index 'IX_CustomerName'. The duplicate key value is (Jackson, Pter).
The statement has been terminated.
Upon deleting right after that I get the message:
The company has lost customers.
(1 row(s) affected)
(1 row(s) affected)
(0 row(s) affected)
My desired output is to add one row or delete one row. Even if I choose to delete a non existing row it seems to have the affect above. What it seems to be doing is creating more than one row. I have a UNIQUE key on customer name, so if the customer appears more than once an error will occur. How can I get my output for inserting to one row inserted and not the system trying to insert the same row multiple times?
INSERT, one forDELETE- then you don't need to mess around with figuring out what it is, and lots ofIFand stuff like that....'Pter', 'Jackson'data is already present in the table thats y u not allowed to insert since you have a unique index and because u already one row withCustomerFName = 'Pter'it is getting deleted while u are run delete statement.