test_casade.sql

SELECT * FROM PERSON;

DELETE FROM PERSON WHERE SSN = '087472489'

SELECT * FROM ACCOUNT;
SELECT * FROM CORDER;
SELECT * FROM EMPLOYEE;

DELETE FROM ACCOUNT WHERE ID = '0591040376'
DELETE FROM CORDER WHERE SSN_EMP = '749280483'
DELETE FROM ACCOUNT WHERE SSN = '642346527'

DELETE FROM ACCOUNT WHERE SSN = '571938356'
SELECT * FROM EMPLOYEE;
DELETE FROM EMPLOYEE WHERE SSN = '571938356' OR SuperSSN = '571938356'

SET IDENTITY_INSERT CORDER ON
DELETE FROM CORDER WHERE ORDERID = '4380275930' 
SET IDENTITY_INSERT CORDER OFF

update CORDER
set O_IDX = IDENTITY(1,1)


CREATE TRIGGER trigger_deleteOrder ON CORDER AFTER DELETE AS
BEGIN
	DROP TABLE IF EXISTS CORDER_TEMP;
	SELECT * INTO CORDER_TEMP FROM CORDER;
	DELETE FROM CORDER;
	DBCC CHECKIDENT ('CORDER', RESEED, 0);
	SET IDENTITY_INSERT CORDER OFF
	INSERT INTO CORDER(SSN_CR,SSN_CS,SSN_EMP,ORDERID,RATE,PaymentType,STATUS,OTIME) SELECT SSN_CR,SSN_CS,SSN_EMP,ORDERID,RATE,PaymentType,STATUS,OTIME FROM CORDER_TEMP ORDER BY O_IDX;
	SET IDENTITY_INSERT CORDER ON
	SELECT * FROM CORDER;
	DROP TABLE IF EXISTS CORDER_TEMP;	
END

SELECT * FROM CORDER;
DELETE FROM CORDER WHERE ORDERID = '0979647474'


use DRIVER_SERVICE

select * from customer_receive
delete from CUSTOMER_RECEIVE where ssn = '215040471'

select * from ALTERNATIVE_RECEIVER where ssn = '215040471'
select * from corder where ssn_cr = '215040471'

select * from PACKAGE

select * from CUSTOMER_SEND

delete from CUSTOMER_SEND where ssn ='795908059'

select * from CORDER where ssn_cs = '795908059' 

SELECT * FROM EMPLOYEE
select * from employee where ssn = '507735440'
SELECT * FROM EMPLOYEE WHERE SuperSSN = '087472489'
--507735440
select * from CORDER where SSN_EMP = '087472489'

delete from EMPLOYEE where ssn = '384392997'

delete from employee where SuperSSN = '087472489'

update employee 
set SuperSSN = NULL
WHERE SuperSSN = '571938356'
--159951930, 749280483