Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-05-08 : 15:24:15
|
Ezra writes "I have a database called 'Device01'. It is built in Microsoft SQL server 7.0. I've got plenty of tables in the database but I'm only using two of them for the sake of testing the trigger. The two tables are named 'CLIENT' and 'CONVERTERSTRANSISTORS'. The client table has a primary key, CLIENT.CLIENTID, and the converterstransistors table a foreign key, CONVERTERSTRANSISTORS.CLIENTID. The database diagram has been drawn already and these relationships has been established. In other words the PRIMARY and FOREIGN -KEY constraints has already been built. My trigger is on the CLIENT table and has the following code:
CREATE TRIGGER CLIENT_CASCADED_DELETE ON CLIENT
FOR DELETE
AS
DELETE FROM CONVERTERSTRANSISTORS
WHERE CONVERTERSTRANSISTORS.CLIENTID = CLIENTID
When I try and test it by deleting a row from the CLIENT table with a corresponding foreign key in the CONVERTERSTRANSISTORS table, I end up with the following error message:
[MICROSOFT][ODBC SQL SERVER DRIVER][SQL SERVER] DELETE statement conflicted with COLUMN REFERENCE CONSTRAINT
'FK_CONVERTERSTRANSISTORS_CLIENT'. The conflict occurred in database 'DEVICE01', table 'CONVERTERSTRANSISTORS',
column 'CLIENTID'.
Firstly I don't know if my trigger is right and secondly I've read that if there is primary and foreign key constraints on the table with the trigger on a column the constraints will take precedence and the trigger will not be fired should the relationship be attempted to brake.
What can you suggest I do for the second instance? and is the trigger formulated correctly?" |
|
|
|
|
|
Cascaded delete trigger