AbleCommerce Shopping Cart Software

Code: Select all

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'ac_Orders'
AND CONSTRAINT_NAME = 'ac_Users_ac_Orders_FK1')
BEGIN
	ALTER TABLE ac_Orders
	DROP CONSTRAINT ac_Users_ac_Orders_FK1
END
GO


ALTER TABLE ac_Orders ADD CONSTRAINT
	ac_Users_ac_Orders_FK1 FOREIGN KEY
	(
	UserId
	) REFERENCES ac_Users
	(
	UserId
	) ON UPDATE  NO ACTION 
	 ON DELETE  SET NULL 
GO

Code: Select all

/* STEP 2: Remove existing constraint */
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'ac_Orders'
AND CONSTRAINT_NAME = 'ac_Users_ac_Orders_FK1')
BEGIN
   ALTER TABLE ac_Orders
   DROP CONSTRAINT ac_Users_ac_Orders_FK1
END
GO

/* STEP 3: Add the correct constraint */
alter table "ac_Orders"
with nocheck
add constraint "ac_Users_ac_Orders_FK1" foreign key ("UserId")
references "ac_Users" ("UserId") on update no action  
go

/* STEP 4. Drop existing trigger for user deletes */
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'ac_Usersdelete' AND type = 'TR')
    DROP TRIGGER ac_Usersdelete
GO

/* STEP 5. 5. Add correct trigger for user deletes */
Create trigger "ac_Usersdelete" on "ac_Users" 
for delete 
as
BEGIN
  declare
	@errorNumber int,
	@errorMsg varchar(255)

  /* Clause for ON DELETE to referenced table SET NULL */
  update "ac_AuditEvents"
  set "ac_AuditEvents"."UserId" = NULL
  from deleted,"ac_AuditEvents"
  where "ac_AuditEvents"."UserId" = deleted."UserId"

  /* Clause for ON DELETE to referenced table SET NULL */
  update "ac_OrderNotes"
  set "ac_OrderNotes"."UserId" = NULL
  from deleted,"ac_OrderNotes"
  where "ac_OrderNotes"."UserId" = deleted."UserId"

  /* Clause for ON DELETE to referenced table SET NULL */
  update "ac_Orders"
  set "ac_Orders"."UserId" = NULL
  from deleted,"ac_Orders"
  where "ac_Orders"."UserId" = deleted."UserId"

  return
  errorHandler:
    raiserror @errorNumber @errorMsg
  rollback transaction
END
go