StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
20328107
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
1
CommunityOwnedDate
CreationDate
2013-12-02T12:16:06.823
FavoriteCount
0
LastActivityDate
2013-12-02T12:27:07.517
LastEditDate
2013-12-02T12:27:07.517
LastEditorUserId
2693006
OwnerUserId
2693006
ParentId
20326520
PostTypeId
2
Score
0
ViewCount
0
LastEditorDisplayName
text
Body
It's quite problematic to achieve what you want, but not impossible. The deal here is that you must deal with any possible constraints before setting the key to null. I believe we need to use a little dynamic SQL here if we want to achieve a solution. Here's the modified version of your trigger I quickly adjusted (Note: I haven't tested it): <pre><code>create or replace trigger UPDATE_EMPLOYEES_DEPT_ID before update or delete of DEPARTMENT_ID on DEPARTMENTS for each row begin if UPDATING then update EMPLOYEES set DEPARTMENT_ID = :new.DEPARTMENT_ID where DEPARTMENT_ID = :OLD.DEPARTMENT_ID; elsif DELETING then for i in ( select CONSTRAINT_NAME from USER_CONSTRAINTS where CONSTRAINT_TYPE='R' and R_CONSTRAINT_NAME in (select CONSTRAINT_NAME from USER_CONSTRAINTS where CONSTRAINT_TYPE in ('P','U') and TABLE_NAME='EMPLOYEES') ) loop for j in ( select TABLE_NAME, COLUMN_NAME from USER_CONS_COLUMNS where CONSTRAINT_NAME = i.CONSTRAINT_NAME ) loop update j.TABLE_NAME set j.COLUMN_NAME = null where j.COLUMN_NAME = :OLD.DEPARTMENT_ID; end loop; end loop; update EMPLOYEES set DEPARTMENT_ID = null where DEPARTMENT_ID = :OLD.DEPARTMENT_ID; end if; end; </code></pre> Let me explain a little how it works regarding the delete: <ul> <li>First it grabs all the foreign keys referencing the table 'EMPLOYEES'</li> <li>Then for each foreign key found it grabs the table name and column it references</li> <li>Then for each table-column pair it nulls the values of those fields which match the :OLD.DEPARTMENT_ID</li> <li>Finally after dealing with all the foreign keys it nulls the DEPARTMENT_ID in the EMPLOYEES table as you had planned initially.</li> </ul> As I haven't really tested this, let me know if this causes any problems.
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POConstraint violation error while executing before update trigger
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. USCeiling Gecko
UserOwnerUserId
1. USCeiling Gecko
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId
1. COFortunately this won't work! You really don't want to update child records blindly. Also this is not how dynamic SQL works.
 singulars
 PostPostId
 PO
 UserUserId
 USVincent Malgrat

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.