Referential Constraint Vs Trigger
Jan 18, 2007
I am using SQL server 2000. I have a parent table 'MimeTypes' with primary key 'mimetype'. This is referenced as foreign key in table 'AssetTypes' as column 'BaseMimeType'.
The referential constraint is simple :
alter table AssetTypes
add constraint FK_ASSETTYPES_REF_BASEMIMETYPE foreign key (BaseMimeType)
references MimeTypes (MimeType) on update no action on delete no action;
I have FOR Update trigger on parent table ('MimeTypes') which is as follows:
CREATE TRIGGER trg_MimeTypes_update ON MimeTypes FOR UPDATE
AS
IF UPDATE(MimeType)
BEGIN
UPDATE AssetTypes
SET BaseMimeType = (select MimeType from inserted)
FROM AssetTypes as A
JOIN deleted as d on A.BaseMimeType = d.MimeType
END;
********
The problem is simple. I want to execute a update query on parent table ('MimeTypes') which modifies 'mimetype' value referenced by child. I dont want to include cascade option in constraint.
When such query is executed error comes as : UPDATE statement conflicted with COLUMN REFERENCE constraint 'FK_ASSETTYPES_REF_BASEMIMETYPE'. The conflict occurred in database 'default.db', table 'AssetTypes', column 'BaseMimeType'.
*****
Is there any way to do this?
View 5 Replies
ADVERTISEMENT
Feb 28, 2008
Hi there,
I am looking for a way to define a trigger that is a replacement for a multi-column foreign key.
I know how to a convert a single-column foreign key constraint into a trigger (i.e., to resolve diamond-structured references).
CREATE TABLE parent_tab
(
col_a INTEGER NOT NULL,
CONSTRAINT pk PRIMARY KEY(col_a)
);
CREATE TABLE child_tab
(
col_x INTEGER NOT NULL,
CONSTRAINT fk FOREIGN KEY (col_x) REFERENCES parent_tab(col_a) ON DELETE CASCADE
);
The conversion would remove the foreign key definition and add this trigger:
CREATE TRIGGER tr_single
ON parent_tab INSTEAD OF DELETE
AS BEGIN
DELETE FROM child_tab WHERE (child_tab.col_x IN (SELECT col_a FROM deleted))
DELETE FROM parent_tab WHERE (parent_tab.col_a IN (SELECT col_a FROM deleted))
END;
Unfortunately, now I need to resolve a situation where there is involved a multi-column foreign key.
CREATE TABLE parent_tab
(
col_a INTEGER NOT NULL,
col_b INTEGER NOT NULL,
CONSTRAINT pk PRIMARY KEY(col_a, col_b)
);
CREATE TABLE child_tab
(
col_x INTEGER NOT NULL,
col_y INTEGER NOT NULL,
CONSTRAINT fk FOREIGN KEY (col_x, col_y) REFERENCES parent_tab(col_a, col_b) ON DELETE CASCADE
);
This does not work, because the temporary table "deleted" might contain more than one row. How do I make sure that the values belong to the same row?
-- incorrect trigger, might delete too many rows
CREATE TRIGGER tr_single
ON parent_tab INSTEAD OF DELETE
AS BEGIN
DELETE FROM child_tab WHERE (child_tab.col_x IN (SELECT col_a FROM deleted) AND child_tab.col_y IN (SELECT col_b FROM deleted))
DELETE FROM parent_tab WHERE (parent_tab.col_a IN (SELECT col_a FROM deleted) AND parent_tab.col_b IN (SELECT col_b FROM deleted))
END;
-- some magic needed :-)
CREATE TRIGGER tr_single
ON parent_tab INSTEAD OF DELETE
AS BEGIN
DELETE FROM child_tab WHERE (child_tab.col_x IN (SELECT col_a FROM deleted AS t1) AND child_tab.col_y IN (SELECT col_b FROM deleted AS t2) AND row_id(t1) = row_id(t2))
DELETE FROM parent_tab WHERE (parent_tab.col_a IN (SELECT col_a FROM deleted AS t1) AND parent_tab.col_b IN (SELECT col_b FROM deleted AS t2) AND row_id(t1) = row_id(t2))
END;
I know the trigger definition above is ***... but I hope that it helps to make clear what I need.
Btw., I use SQL Server 2005.
Thanks in advance,
slowjoe
View 3 Replies
View Related
Dec 7, 2007
Is it possible to define a referential constraint between two tables in two different databases (on two servers)? Or are there beter best practices methods/products to achieve this result.
View 4 Replies
View Related
Nov 10, 1998
I'm not sure about the way you enforce the referential integrity in a SQL server 6.5 database (I'm new to this environment). Does any one have an exemple. The doc I have is useless...Thanks
View 1 Replies
View Related
Oct 4, 2000
Hi,
I need to write a referential integrity trigger. I have two tables called Table1 and Table2. In both the tables i have common column called FieldA.
Now i need to write a trigger, whenever i updates Table1 this trigger automatically needs to update a FieldA in Table2.
Can anyone give me Code or suggestions , how to write this trigger. I am new to triggers.
Thank you!
---Ram
View 1 Replies
View Related
Oct 4, 2000
Hi,
I need to write a referential integrity trigger. I have two tables called Table1 and Table2. In both the tables i have common column called FieldA.
Now i need to write a trigger, whenever i updates Table1 this trigger automatically needs to update a FieldA in Table2.
Can anyone give me Code or suggestions , how to write this trigger. I am new to triggers.
Thank you!
I know how to write a normal insert , update, delete triggers and using help from books online.. SO pls do it in useful.
---Ram
View 1 Replies
View Related
Nov 13, 2006
I am having great difficulty with cascading deletes, delete triggers and referential integrity.
The database is in First Normal Form.
I have some tables that are child tables with two foreign keyes to two different parent tables, for example:
Table A
/
Table B Table C
/
Table D
So if I try to turn on cascading deletes for A/B, A/C, B/D and C/D relationships, I get an error that I cannot have cascading delete because it would create multiple cascade paths. I do understand why this is happening. If I delete a row in Table A, I want it to delete child rows in Table B and table C, and then child rows in table D as well. But if I delete a row in Table C, I want it to delete child rows in Table D, and if I delete a row in Table B, I want it to also delete child rows in Table D.
SQL sees this as cyclical, because if I delete a row in table A, both table B and table C would try to delete their child rows in table D.
Ok, so I thought, no biggie, I'll just use delete triggers. So I created delete triggers that will delete child rows in table B and table C when deleting a row in table A. Then I created triggers in both Table B and Table C that would delete child rows in Table D.
When I try to delete a row in table A, B or C, I get the error "Delete Statement Conflicted with COLUMN REFERENCE". This does not make sense to me, can anyone explain? I have a trigger in place that should be deleting the child rows before it attempts to delete the parent row...isn't that the whole point of delete triggers?????
This is an example of my delete trigger:
CREATE TRIGGER [DeleteA] ON A
FOR DELETE
AS
Delete from B where MeetingID = ID;
Delete from C where MeetingID = ID;
And then Table B and C both have delete triggers to delete child rows in table D. But it never gets to that point, none of the triggers execute because the above error happens first.
So if I then go into the relationships, and deselect the option for "Enforce relationship for INSERTs and UPDATEs" these triggers all work just fine. Only problem is that now I have no referential integrity and I can simply create unrestrained child rows that do not reference actual foreign keys in the parent table.
So the question is, how do I maintain referential integrity and also have the database delete child rows, keeping in mind that the cascading deletes will not work because of the multiple cascade paths (which are certainly required).
Hope this makes sense...
Thanks,
Josh
View 6 Replies
View Related
Feb 9, 2004
I have 2 tables
Primary_Table (Key, ........)
Foreign_Table (Key_1, Key_2, ............)
I want this:
If I update the Key in Primary_Table then
Key_1 is Update where Key_1 = Key
AND
If I update the Key in Primary_Table then
Key_2 is Update where Key_2 = Key
I can not use CONSTRAINT because I can not use ON UPDATE CASCADE for this case (It not possible to use 2 CONSTRAINT WITH ON UPDATE CASCADE).
I WANT TO USE TRIGGERS
This is my trigger (but I am not sure it is the best way ?)
CREATE TRIGGER Test ON [Primary_table]
FOR UPDATE
AS
Declare @NewValue varchar(30)
Declare @OldValue varchar(30)
SET @NewValue = (Select Key From Inserted)
SET @OldValue = (Select Key From Deleted)
UPDATE [Foreign_Table]
SET [Foreign_Table].[Key_1] = @NewValue
FROM [Foreign_Table] WHERE [Key_1] = @OldValue
UPDATE [Foreign_Table]
SET [Foreign_Table].[Key_2] = @NewValue
FROM [Foreign_Table] WHERE [Key_2] = @OldValue
Sorry for my english.
Please consider I am a beginner
View 1 Replies
View Related
Aug 8, 2007
Hello,
I have a table that allows a user (from another table) to belong to multiple organizations. Part of this table's schema is a column that will be used to allow the user to indicate which organization that they belong to will be the primary one. A user can only have one PRIMARY organization. I was going to handle via an Insert or Update Trigger...checking to see if the userid already a Primary. However, now I am wondering if a Constraint would be the way to go? I admit though, I don't know enough about Constraints to know how this would work.
The table's schema is like:
UserOrg
UserOrgID (an identity column)
UserID (foreign key to the user's table)
Org (the name of the org)
PrimaryOrg (bit column)
Thanks for any help.
- Will.
View 3 Replies
View Related
May 3, 2007
I do not know this is the correct way to do this, but somehow thisisnt working. All I want is not to have a null value in field A ifthere is a value in field Bheres the codeCREATE TRIGGER tiu_name ON tblNameFOR INSERT, UPDATEASDECLARE @FieldA AS REAL, @FieldB AS REAL;SELECT @FieldA=FieldA, @FieldB=FieldBFROM Inserted;IF (@FieldB IS NOT NULL) AND (@FieldA IS NULL)RAISERROR('Error Message',1,2);GOPlease Help.
View 1 Replies
View Related
May 12, 2007
Hi Guys,
I am not sure if this is the correct forum but i thought i'd ask and see if you can help me!
I have a table with 2 columns:
1st column will house numbers from 1 to 50
2nd column will be date
I want the users to be able to pick a number for certain date and enter it to the table, however I don't want the system to allow the same number for the same date. I was looking at constraints and triggers but can't make out what exactly i should use and how. The Insert will be initiated from ASP page on our intranet. Please help!!!
Regards
SD
View 1 Replies
View Related
May 1, 2008
Hello all
The majority of my database experience comes from ORACLE and i am trying to use some functionality that i have already used in Oracle into a project i am working on in MSDE.
I want to create a trigger that uses a DML constraint that will prevent a tenants from being inserted into a house if the bedroom count is less or equal to the number of tenants currently in the house.
The oracle code is below
CREATE OR REPLACE TRIGGER Tenant_room_check
BEFORE INSERT or update of tenant_ID ON Tenant
FOR each row
as (new.tenant_ID is not null)
DECLARE
Tenant_count NUMBER;
Bedroom_count NUMBER;
BEGIN
select count(Tenant_ID) as Tenant_count
from Tenant
where tenant_ID = :new.tenant_ID
and House_ID = 1
AND Tenant_status = 1;
select count(ROOM_ID) as bedroom_count
from Room
where Room_Name = 'Bedroom'
and House_ID = 1
if (Tenant_count > Bedroom_count)
then raise_application_error(-20601,
'you cannot have more tenants than the number of bedrooms in a student house');
END IF;
END;
/
Ideally I would like to pass the HOUSE_ID and the TENANT_ID from my application using @variablename
I have been looking over forums and in books but i am getting all confused over the syntax to use.
Please Help
Many Thanks
Quish
View 5 Replies
View Related
Nov 24, 2006
In my Projecti want to check the date at the time of insert in A-Tablethat it should be Greater than (>) Date Defined in B-TableNote:-B-table have only one record so plz tell me how can i do using Sql-Server Backend only
View 3 Replies
View Related
Mar 1, 2005
say i have a query like:
UPDATE table SET status = 1 WHERE id = 1000
if i have a trigger on that table, is there anyway i can get the id ?
i know i can get the status by SELECT status FROM Inserted, but anyway to get what the id is? or would i just have to update the id as well?
thanks
View 4 Replies
View Related
Aug 7, 2014
I want to incorporate a Check constraint within a trigger, based on this but im struggling with the coding.Assuming that is an Alphanumeric field you should be checking what value is in the alphanumeric column from inserted, comparing it with whatever is in the AMGR_User_Fields_Tbl to see if there’s a duplicate and then raising the error...This is my Trigger:
ALTER TRIGGER [dbo].[DUPLICATES]
ON [dbo].[AMGR_User_Fields_Tbl]
FOR INSERT, UPDATE
AS
DECLARE @Alphanumericcol VARCHAR (750)
-- This trigger has been created to check that duplicate rows are not inserted into table.
-- Check if row exists
SELECT @Alphanumericcol
FROM Inserted i, AMGR_User_Fields_Tbl t
[code]....
View 1 Replies
View Related
Jul 5, 2000
Hi,
Can anyone tell me the order in which uniqueness constraints on indexes are enforced vs. when triggers are executed ? I have a unique constraint on an index and a trigger on the column on which the same index has been created. When a row is inserted, the trigger checks if the value for that column already exists in the table - if not, it inserts the row as is, else it gets the max() val of the column (based on another key column) and increments it by one, then does the insert. Creating an index across the two works fine, but if I set the Unique Values property for the index, subsequent inserts bomb out - yet there aren't any duplicates in the final table, as the trigger ensures this. Anyone got any ideas on this? My deduction is that the uniqueness constraint gets enforced before the trigger gets executed, but at the same time this *seems* illogical, as the row has not been inserted into the table at the point where the trigger is executed.
Regards,
Jon Reade.
View 2 Replies
View Related
Feb 23, 2007
Hi.
I was wondering how I should go about doing this thing. I need to put a value in a column that is based on values on other columns in the same row.
When this statement executes I need to put a value in Col3.
insert into myTable(Col1, Col2)
values(25, -14)
Something like so:
if(Col1 >0 AND Col2 <0)
set Col3 = Col1 - Col2
else
set Col3 = Col1;
I don't now quite how to solve this. I am really going to need this value in a column. Calculating the value at retrieval is not on option...
I appreciate all help. I'm using SQL Server 2005.
Thanks!
View 2 Replies
View Related
Oct 12, 2014
how to i write a trigger to enforce this constraint..A rental can be made only if the customer is registered with the company and the car is not currently rented out. If not, the rental will not be successful.
View 1 Replies
View Related
Jul 17, 2001
Hello all.
First of all, I've been a reader of swynk.com for quite sometime now, and I'd like to say 'thank you' to everyone who contributes.
Today, I'm the town moron.. haha I'm having issues with column level constraints. I have a varchar(50) where I want to keep *,=,#,/, .. etc, OUT OF the value input. I don't want to strip them. I simply want for sql to throw an error if the insert contains those (and other characters). The only characters that I want in the column are A-Z and 0-9. However, it's not a set number of characters per insert. It always varies... There has to be an easier way to do this than creating a constraint for every possibilty... Any help would be greatly appreciated.
tia,
Jeremy
View 1 Replies
View Related
Mar 16, 2007
Using the new referential integrity constraints that will be made available, will it allow us to manually define the relationships between entities even if there is no true foreign key constraints setup in the database?
Lets say we deleted the FK_Orders_Customers in Northwind between orders and customers.
Or is this ability available now?
Thank for your time.
View 2 Replies
View Related
May 13, 2008
Hi, all.
I am trying to create table with following SQL script:
Code Snippet
create table Projects(
ID smallint identity (0, 1) constraint PK_Projects primary key,
Name nvarchar (255) constraint NN_Prj_Name not null,
Creator nvarchar (255),
CreateDate datetime
);
When I execute this script I get following error message:
Error source: SQL Server Compact ADO.NET Data Provider
Error message: Named Constraint is not supported for this type of constraint. [ Constraint Name = NN_Prj_Name ]
I looked in the SQL Server Books Online and saw following:
CREATE TABLE (SQL Server Compact)
...
< column_constraint > ::= [ CONSTRAINT constraint_name ] { [ NULL | NOT NULL ] | [ PRIMARY KEY | UNIQUE ] | REFERENCES ref_table [ ( ref_column ) ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ]
As I understand according to documentation named constraints should be supported, however error message says opposite. I can rephrase SQL script by removing named constraint.
Code Snippet
create table Projects(
ID smallint identity (0, 1) constraint PK_Projects primary key,
Name nvarchar (255) not null,
Creator nvarchar (255),
CreateDate datetime
);
This script executes correctly, however I want named constraints and this does not satisfy me.
View 1 Replies
View Related
Aug 11, 2001
Question:
What is used to enforce referential integrity ?
Answer: Triggers; Foriegn Keys.
what is Foreign Keys ?
View 1 Replies
View Related
Oct 17, 2001
How do you enforce referential integrity in SQL between tables?
Thanks.
View 1 Replies
View Related
Jan 9, 2007
I am new to MS SQL2005 but I have started working with it and found a difference between what MS Access and MS SQL will allow. I have a large DB with many tables but my problem relates to one specific issue. I have a table named tblAreaCodes which holds all area codes for the US. I have another table named tblSupplier where there are multiple colums such as name address etc. and three columns named phac, ph2ac, orderac, and faxac. All are the same type (Int) as is the Primary key in the tblAreaCode table. I have linked each of the columns in the tblSupplier table back to the primary in the tblAreaCode table. All is fine except I can not set the relationship to cascade update or delete. I am unsure what this means or how to proceed since this works and is allowed in MS Access. Any help and or direction would be greatly appreciated.
LKP
View 3 Replies
View Related
Feb 26, 2008
I used to work with little databases and throw everything into a table with no relation, and modify them all individually. Now I've learned about referential integrity, makes things a lot easier.
My question is, now what? Say I have a Customers table and an Orders table (to keep it simple). If custID is the primary key in customers, foreign key in orders, then if I want to insert into Orders, I need the custID. So those kinds of things I need to keep stored in a session state and insert them like that, correct?
View 1 Replies
View Related
Oct 26, 2004
Hi guys,
Is there a way of finding the foreign keys for a table and therefore determine weather any referential integrity rules would be broken if a record was deleted?
For example. You have an author you want to delete, but that author has books. You call a procedure to delete the author. The stored procedure checks the foriegn keys, checks the tables and determines what is in them and if any tables have records that would be "orphaned" you return an error reporting what tables have the "would be orphaned" data in them.
It need to be fairly generic int he manner that it checks the foreign keys and the sub table data.
This is on behalf of a guy at work so "requirements" may change. ;)
Anyone got some ideas??
Cheers,
Shaun
View 5 Replies
View Related
Feb 1, 2008
hi guys,
i was asked to drop some tables...but before dropping i was asked to check for referential integrity..
so where and how can i check this referential integrity????
View 2 Replies
View Related
Dec 23, 2005
Hihow to set the referential integrity between 2 tables using enterprisemanager (microsoft SQL SERVER).. i tried and the tab doesn't allow meto choose. pls.help.thankssree--chavasreedharMessage posted via http://www.exforsys.com for all your training needs.
View 1 Replies
View Related
Jan 10, 2004
I'm trying out this database layout under MS SQL (de), with primary multicolomn key, but ....
Let's say table A, has primary KEY on the field PK_A
Let's say table B, has primary KEY on the field PK_B
Now table C, has fields ID_A and ID_B, defined as multicolomn primary key, referencing obviuosly to the PK_A and PK_B values.
NOW THE PROBLEM !! :(
Let present you table D , with his beautiful field ID_C. :)
How can I succed in referencing with the ID_C field the primary multicolomn key of table C ??
I' designing the whole thing with access97+mde, but dragging the PK key of table C (ID_A & ID_B) over the table D (ID_C) doesn't work !!
Any help ??
Thanks in advance !!
H2O
View 5 Replies
View Related
Apr 21, 2008
What ate the method/techniques i can use for enforcing referential integrity, Without using foreign key constraints ?
View 7 Replies
View Related
Jun 6, 2008
Hi, I tried to delete a record in tblA which has an ID in tblB, i got error msg saying referential problem, please tell me what should i do? thanks.
View 2 Replies
View Related
Jun 3, 2006
I'm using Microsoft SQL Server Management Studio Express 9.00.2047.00and expriencing problems with setting referential integrity on a linktable. The tables' schema is as follows:-------------------------------------------------------------------CREATE TABLE competencies (CID bigint identity(1,1) CONSTRAINT pk_CID PRIMARY KEY,LockedBy bigint DEFAULT 0 NOT NULLCONSTRAINT fk_UserIDREFERENCES usr_info(userID)ON DELETE SET DEFAULTON UPDATE CASCADE)---------------------------------------------------------CREATE TABLE usr_info (userID bigint IDENTITY(0,1) CONSTRAINT pk_UID PRIMARY KEY,ActiveFlag bit default 0 NOT NULL, --(1='Yes', 0='No')FirstName varchar(100) default '' NOT NULL,LastName varchar(100) default '' NOT NULL)-------------------------------------------------------CREATE TABLE competency_hdr (fkCID bigint default 0 NOT NULLCONSTRAINT fkCID_chREFERENCES competencies(CID)ON DELETE CASCADEON UPDATE CASCADE,ApprovedBy bigint default 0 NOT NULLCONSTRAINT fkUserID_chREFERENCES usr_info(userID)ON DELETE SET DEFAULT -- NO delete if user is deletedON UPDATE CASCADE)--------------------------------------------------------When I execute the above I get the following error message.Msg 1785, Level 16, State 0, Line 1Introducing FOREIGN KEY constraint 'fkUserID_ch' on table'competency_hdr' may cause cycles or multiple cascade paths. SpecifyON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGNKEY constraints.Msg 1750, Level 16, State 0, Line 1Could not create constraint. See previous errors.Now, if i swap the fields around then the error message changes tothat of the fkCID field.Basically what I want is:when I delete a competency record I need all references to this recordto be deleted.when I delete a user I want to set the foreign key to zero (the recordmust remain on the database).Obviously there is something I'm missing here. Any advice, anyone?---------------------------------------------------------------Join Bytes! : Remove your pants to reply---------------------------------------------------------------
View 6 Replies
View Related
May 21, 2007
Hello every body,I need some clarification of concept regarding self referentialtables.consider we have a "Person" table that stores data about employees ofan organisation.in case a person change his name or any details we dont want to updateour database by loosing old information and adding new one. what weneed is to hold previous details as well as the new ones.theoratically for me its easy i will just add another row and linkthat row with one of the existing row. to do so i created a relationbetween the "Emp_id" column of "Person" table to itself. so one"Emp_id" could be related to another "Emp_id" in the same table.after doing so i dont know how can i indicate while inserting a recordthat this new record in linked with one of the previous records. meansin insert statement how the relation ship would be added.here i am not sure if i am thinking in the right direction or not aswe may need to add another table or another column to indicate therelation between old an new row. but if we have to add that new columnsay "old_Emp_id" in "Person" table then what does the relationshipbetween "Emp_id" with itself serves.thanksAli
View 4 Replies
View Related