0

I am trying to learn SQL, and while my journey has been quite successful this far, I have run into problems with triggers. For the record, I am using SQL Server 2016. I would appreciate any help I can get. Please let me know if more details are needed.

Monsieur M.
  • 11
  • 3
  • have you tried the cascade delete? – Nove Sanchez Sep 08 '17 at 14:52
  • 1
    That seems like a very bad idea. Deleting the order would be removing the transaction from your system for a transaction that occurred. Not a great idea. – Sean Lange Sep 08 '17 at 15:01
  • General question. Why would you delete the customer? Was it an entry error? Perhaps customers can only be deleted when no orders are tied to that customer... this could be a proposed rule. You could go a another step and flag existing customers with orders as IsDeleted=true. – Ross Bush Sep 08 '17 at 15:08
  • Welcome to Stack Overflow! Please read [How do I ask a good question?](http://stackoverflow.com/help/how-to-ask) before attempting to ask more questions. This is **not** a free *send me teh codez* site, you have to make an effort. –  Sep 08 '17 at 15:33
  • Welcome to Stack Overflow! [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/questions/261592/how-much-research-effort-is-expected-of-stack-overflow-users) The answer, *"A lot. An absurd amount. More than you think you are capable of. After you have reached the end of your rope and the pain of not having the answer exceeds the vast amount of shame received by posting your question, that's when you can go ahead and ask. Because at that point, you will have done whatever research necessary to make it a good question worth asking."* –  Sep 08 '17 at 15:33
  • Welcome to Stack Overflow! Please read [What types of questions should I avoid asking?](http://stackoverflow.com/help/dont-ask) before attempting to ask more questions. –  Sep 08 '17 at 15:34

2 Answers2

-1

It is much safer if you would simply add a deleted flag in your customer table and in your trigger just update the deleted column to true.

But if you really need to delete the child records after the parent record is deleted, try using the cascade delete. You can refer from to this old post. How do I use cascade delete with SQL Server?

Nove Sanchez
  • 109
  • 5
-1

Hope it will help

-------------
--cascade delete way
-------------

CREATE TABLE dbo.customers (
       customer_id INT NOT NULL
     , somedata    UNIQUEIDENTIFIER NOT NULL 
     , CONSTRAINT PK_Customer PRIMARY KEY (customer_id)
     );

CREATE TABLE dbo.orders (
       OrderID     INT NOT NULL
     , customer_id INT NOT NULL
     , somedata    UNIQUEIDENTIFIER NOT NULL 
     , CONSTRAINT PK_Order PRIMARY KEY (OrderID)
     , CONSTRAINT FK_CustomerOrder FOREIGN KEY (customer_id) 
REFERENCES dbo.customers (customer_id) ON DELETE CASCADE
     );

INSERT INTO dbo.customers (customer_id,somedata)
VALUES (1,NEWID()),(2,NEWID())

INSERT INTO dbo.orders(OrderID,customer_id,somedata)
VALUES (1,1,NEWID()),(2,2,NEWID())

DELETE FROM dbo.customers WHERE customer_id = 1

SELECT * FROM dbo.orders

-------------
--trigger way
-------------

CREATE TABLE dbo.customers1 (
       customer_id INT NOT NULL
     , somedata    UNIQUEIDENTIFIER NOT NULL 
     , CONSTRAINT PK_Customer1 PRIMARY KEY (customer_id)
     );

CREATE TABLE dbo.orders1 (
       OrderID     INT NOT NULL
     , customer_id INT NOT NULL
     , somedata    UNIQUEIDENTIFIER NOT NULL 
     , CONSTRAINT PK_Order1 PRIMARY KEY (OrderID)
     , CONSTRAINT FK_CustomerOrder1 FOREIGN KEY (customer_id) 
REFERENCES dbo.customers1 (customer_id) ON DELETE CASCADE
     );

GO

CREATE TRIGGER DELTRIG
ON  dbo.customers1
FOR DELETE
AS
     DELETE C
     FROM dbo.orders1 AS C
        INNER JOIN DELETED ON
            C.customer_id = DELETED.customer_id

GO

INSERT INTO dbo.customers1 (customer_id,somedata)
VALUES (1,NEWID()),(2,NEWID())

INSERT INTO dbo.orders1(OrderID,customer_id,somedata)
VALUES (1,1,NEWID()),(2,2,NEWID())


DELETE FROM dbo.customers1 WHERE customer_id = 1

SELECT * FROM dbo.orders1
Alexey Sumin
  • 62
  • 2