0

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?

5
  • Why don't you just create two separate triggers - one for INSERT, one for DELETE - then you don't need to mess around with figuring out what it is, and lots of IF and stuff like that.... Commented Nov 11, 2014 at 17:07
  • Can you check my above code? I understand your suggestion and will change my code upon getting my new code to function desirably. Commented Nov 11, 2014 at 17:47
  • '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 with CustomerFName = 'Pter' it is getting deleted while u are run delete statement. Commented Nov 11, 2014 at 17:52
  • Before I run the script, I have no one named Pter. After I run the insert operation it does that. I dropped my unique index from customer name and the error disappeared, but there are two rows being affected on insert. Is that from the insert table and the customers table combined? Commented Nov 11, 2014 at 18:02
  • Also, the row doesn't even physically insert into my customers table. I don't know what to do here. Commented Nov 11, 2014 at 18:04

1 Answer 1

2

How about using special tables Inserted or Deleted

If exists (select 1 from inserted)
    PRINT 'The company has gained customers.'
ELSE
    PRINT 'The company has lost customers.'
Sign up to request clarification or add additional context in comments.

8 Comments

If I do use that, it gets me closer, but then I have the program saying this. 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.
@ClintonScott without looking at the table schema this cannot be fixed. You have created a Unique index on CustomerName. It looks like already table has those values 'Jackson, Pter' you are trying to insert them again.
Let me know how I can send you the schema and I will. Should I post it here somewhere? I asure you that I am not attempting to insert the same row values again.
@ClintonScott Add the customer table structure ,constraints, index etc.. it in the question
I just added the relational schema.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.