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.
My company is upgrading one of our products; this involves some data migration and a question about existing indexes with uniqueness constraints has been raised. Here is the issue:
We have a database table with two particular columns that that we index with a uniqueness constraint. My question is: is the uniqueness constraint case sensitive?
For instance, if I have a database that is using a collation of SQL_Latin1_General_CP1_CS_AS. (case sensitive) and I insert data in these two columns as follows:
test col2test
Test col2test
would this uniqueness constraint be violated even though I'm using a case sensitive collation? I've done tests on this issue and I can't seem to insert this 2nd column; I'm just wanting to be certain that this is the case 100% of the time.
I"m trying to use a BULK INSERT command to insert data into a table from a file. There is a UNIQUE Index that is being violated and the BULK INSERT fails.
I do not want to drop or disable the index, however, i also do not want to load 'duplicate' records so i keep the CHECK_CONSTRAINTS parameter.
Is there a way to have the duplicate records outputed to the ERRORFILE ?
INSERT INTO PurchaseOrder (PurchaseOrderDate, SupplierID) VALUES(@date, @SupplierID)
END
SET @POno = @@IDENTITY
RETURN
However, how do i make it that it will automatically adds item under the POno being gernerated? can i use a trigger so that whenever a Insert for PO is success, it automaticallys proceed to adding the items into the table PurcahseOrderItem?
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)
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.
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!!!
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.
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
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?
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
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.
I am really puzzled by an apparent difference between table index key column order and its statistics order. I was under understanding that index statistics mirror index definition. However, in my db 2470 index ordinal definitions match statistics definition but 66 do not. I also can reproduce such discrepancy in 2008 R2, 2012 and 2014.
As per definition,
stats_column_id int
1-based ordinal within set of stats columns
This script duplicates this for me.
BEGIN TRAN GO use tempdb GO CREATE TABLE [dbo].[ItemProperties]( [itmID] [int] NOT NULL, [cpID] [smallint] NOT NULL, [ipuID] [tinyint] NOT NULL,
[Code] ....
The result I get is this:
object_id      stats_name                                     stats_column_list 1525580473 PK_ItemProperties_itmID_ipuID_cpID itmID, cpID, ipuID,
and
object_id      index_name                                     index_column_list 1525580473 PK_ItemProperties_itmID_ipuID_cpID itmID, ipuID, cpID,
Also a query I used to discover this in my db is:
WITH stat AS ( SELECT s.object_id ,s.name as stats_name ,( SELECT c.name + ', ' as [data()] FROM sys.stats_columns as sc
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.
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.
I am wanting to set up a trigger on our SQL server 2000 to create a sequential order line number in the OrderDetails table for OrderID.
I will be inserting data from an OrderLinesAdd table into the OrderLines table but want the LineNumber field to be incremented by 1 for each group of orders grouped by OrderID that I insert into the table eg.
I have just started using SQL so am not sure how I go about writing the code for the trigger ? It would seem like I will need to use the Max function in the code.
We have an app that uses triggers for auditing. Is there a way to know the order that the records were inserted or deleted? Or maybe a clearer question is.... Can the trigger figure out if it was invoked for a transaction that "inserted and then deleted" a record versus "deleted and then inserted" a record? The order of these is important to our auding.
When i process a order in table Orders_t i would like to check in storage_t if we defenetly have it in storage. .... if we have it in storage, i decrease the "amount" by 1 ..(amount -1), and process the order. Otherwise it will return nothing.
This is what i´ve come up with so far:
CREATE TRIGGER checkInStorage ON orders_t FOR INSERT, UPDATE
AS DECLARE @tOrderId char(3), BEGIN SET @tOrderId = (SELECT orderId FROM INSERTED)
--check if the amount in storage
IF EXIST(SELECT amount FROM storage_t WHERE orderId = @tOrderId and amount >= 0) BEGIN --if it return true, i update the storage by decrease the amount with one UPDATE storage_t SET (amount = amount - 1) WHERE orderId = @tOrderId END
When I select a fieldname in a db as a primary key this will keep the uniqueness of that fieldname, but how is that work when I select 2 fieldnames (in Enterprise Manager) and click on the Key it will mark them as my primary key, but I can insert duplicates values in either fieldname.
this violates the constraint if i give the two teams on the same date..how to give the condition such that it checks for the violation of condition if the team names are interchanged.. for eg
if i give the values as Insert Into Games values('chelsea',1,'arsenal',2,'17-1-2000'); and Insert Into Games values('arsenal',1,'chelsea',2,'17-1-2000');
the above should violate the constraint ..how to do it
OK - maybe my Google-fu is off today but I'm trying to find out if using a sequence table will provide values that are as unique as the GUID is supposed to be.The reason is that I'm building a DW from the ground up (they won't let me expense my booze which I think is grossly unfair ) and rather than using GUID it ~seems~ like a sequence table would give better results. My concern is that the values won't be unique across tables.
Hi,First of all my apologies if you have seen this mail already but I amre-sending as there were some initial problems.This query is related to defining indexes to be unique or not andconsequences thereof.Some documented facts that I am aware of include1. Defining uniqueness allows optimiser to create optimal plans egselect based on keys in such an index allows the optimiser to determineat most only one row will be returned2. Defining uniqueness ensures that rule (business/Primary key) isenforced, regradless of how the data is entered.We have many cases where non unique indexes are defined. The approach todate has been that even though we are aware of some of the benefitsoffered by defining uniqueness , we have chosen not to add keys to nonunique indexes such that they become unique. The primary reason for thiswas that we did not want to make the keys comprising the indexesunnecessarily large and therefore ensuing consequences when DMLstatements are performed.However, I have concerns that having highly duplicate indexes can haveperformance impacts , including deadlocking. I am also aware Sybase usedto store duplicate values in overflow pages and therefore there wereperformance consequences. Could SQL 2000 have the same behaviour ?Thanking you in advancePuvendran*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
So, I have this idea of a database design I was hoping to get some input on. I usually go through the process of creating a separate column for identity, like an auto-increment integer which serves as a context specific ID that easily can be passed around. However one of the short-comings I've come to realize is that the integer is just that, unique to the context and requires context information to make sense. I'm pounding the idea of creating a separate table whose sole purpose is to provide global (database wide) unique IDs. As such each row in a table can still have an ID but instead of being tied to a specific context it will always make sense within the application. A foreign key constraint can ensure that and a relation. Now, with a global ID which I can create FKs to, I don't have to create a separate table for relations between entities and can have relations through these global IDs and I think that's a good thing because if there are many relations between many entities they don't have to be defined more than once. There is also a single "hub" where all relations fit which will allow me to access most information through some relatively simple joins. My concerns are performance and Linq to SQL implication. I'm worried that Linq to SQL might start pulling an excessive amount of data and I'm worried that the practicality of such a design might hurt the long running of the application development process. But I'm surprisingly interested in what it could mean for a more ubiquitous view of data and tagging. Basically a way of any data to have relations to any other data through this yet simple design decision. If you think this is a good idea or see no particular problem with, please let me know! If you think there's problems with my idea, please motivate your critic but do tell me what I should look out for, or if you know of any better approach to my idea of being able to look at the data through some more common mechanism (which applies to all tables and the entire database mostly from a programming perspective).
I want to make sure that usernames and folder names (that are stored in the database) are all unique when created by the admin through an admin control panel.
How would I code and use an Index to make sure that the 2 columns are unique?
I've looked at examples on the net but nothing is telling me how I can create an index through Enterprise Manager? Thanks
I want to make sure that usernames and folder names (that are stored in the database) are all unique when created by the admin through an admin control panel.
How would I code and use an Index to make sure that the 2 columns are unique?
I've looked at examples on the net but nothing is telling me how I can create an index through Enterprise Manager? Thanks :)
Is there a system table column to query to determine whether an index is defined as unique in SQL Server 2000? It is easy to find this info in SQL Server 2005, but I don't see a valid column for this on dbo.sysindexes.
All- I have what i'm sure is a question with a simple answer: Supposing I want to modify the select command below to force the display of records with only unique combinations of the two fields person_id and act_session_id (bolded). How would I do this. Note that neither is a key field. (Key field headcount_id was added for completeness.) Thanks! SELECT hd.headcount_id, hd.person_id, hd.act_session_idFROM headcount as hd
I am reading "SQL Server Query Performance Tuning Distilled",on page 104 it talks about one of the index design recommendationswhich is to choose the column that has very high selectivity of valuesinstead of a column that has very few selectivity of values.My question is if I have currently indexes on my tables that have1, 2, 3, 4, ... values only on thousands of rows, are these nonclusteredindexes pretty much useless indexes that I should get rid of?And I know that pretty much the number of selectivity values willalways remain very low.Thank you
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.
We are using SQL CE 3.5 on tablet PCs, that synchs with our host SQL 2005 Server using Microsoft Synchronization Services. On the tablets, when inserting a record, we get the following error: A duplicate value cannot be inserted into a unique index. [ Table name = refRegTitle,Constraint name = PK_refRegTitle But the only PK on this table is RegTitleID.
The table structure is: [RegTitleID] [int] IDENTITY(1,1) NOT NULL, [RegTitleNumber] [int] NOT NULL, [RegTitleDescription] [varchar](200) NOT NULL, [FacilityTypeID] [int] NOT NULL, [Active] [bit] NOT NULL,
The problem occurs when a Title Number is inserted and a record with that number already exists. There is no unique constraint on Title Number. Has anyone else experienced this?
