I'd like to create a primary key value (incremental) within a trigger and set it in a primary key column.
Any idea anyone? Do I define my trigger as a On INSERT, Instead of INSERT? I tried both but it doesn't seem I'm doing things right.
Hi,
If I want to automatically insert a record which has default value in a table,
how can I create the trigger?
Completely new to SQL so wondered if anyone could help with an issue I'm sure there is a simple answer too.
Need to create a Primary Key for an existing table. Now I have managed to create a new column called _ID, which set the contents to NULL. So populated the column with the number 1 so that I could set it to no null.
Now when I run the query:
ALTER TABLE tablename ADD PRIMARY KEY (_ID);
I get the error:
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo._DATACAPTURE' and the index name 'PK___DATACAPTURE__145C0A3F'. The duplicate key value is (1).
Which is obviously due to the repeated 1 in _ID.
So I am thinking I just need to run a query that fills the _ID with a unique number.
Any ideas how I do that?
Thanks for your help in advance.
Hi
I'm trying to create a composite Primary Key on a table. This is the SQL I've written:
CREATE TABLE BookingItems
(
BookingID INT NOT NULL
REFERENCES Bookings(BookingID),
EquipmentTypeID INT NOT NULL
REFERENCES EquipmentType(EquipmentTypeID),
CONSTRAINT PK_BookingItems_id PRIMARY KEY
(BookingID, EquipmentTypeID)
)
Is this right? I'm trying to define a Primary Key made up of BookingID and EquipmentTypesID, which are both Foreign Keys as defined in the column definition.
Thanks
Jon
Thanks to help from Ray Maio I have been able to create copies of various tables using the Select Into command.
Ray also responded that this command does does not transfer Primary Key and Index information.
I have attempted to create an index by using the following statement:
create nonclustered index SchoolIndx on wrestlerstest (School)
I did not get an error, but can't tell ift the index was created. The question is, how do I find out if
there is an index for the column School? Is there a command that will tell me. I'm just sending commands
to the server via a short .asp program and don't really know how to obtain a message back. Could I find the information
by browsing the
Thanks again in advance,
Greg Zafros
Hi,
I need to create a new table in our database.
This table is not linked into the existing schema in anyway, so i'm not sure if I need a primary key or not.
either way, coudl anyone tell me how to create a primary key ni the CREATE TABLE statement.
I have tried searching but cannot find the answer.
many thanks,
Matt
Hi all,I've spent hours trying to find the error in the following SQL 2000command:ALTER TABLE [ClientList] ADD CONSTRAINT [PK_ClientList] PRIMARY KEYCLUSTERED ([PhoneNo]) On [PRIMARY]Every time I try to "Execute" this from my (VB5) ODBC connection I get:Runtime error 3289;Syntax error in CONSTRAINT clauseFor the life of me I can see nothing wrong. I used Enterprise managerto create this statement, and I can create the primary key fine fromthere. The PhoneNo field does not allow NULLs.Everything is service-packed up to date. I have tried using[databasename].[dbo].[ClientList] and suchlike.I've tried to find a relevant manual, but my SQL Server developersguide suggests this should be ok and I can't see anything wrong in thebooks online.Can anybody please help?TIA
View 7 Replies View RelatedI have create a recursive triggers in tblIncident. The PKID is the primary key in tblIncident, in this trigger, i'm trying to generate an auto increament primary key from the stored procedure GetMaxId and update to tblINcident, but I face a problem where PKID does not refresh the latest PKID, it always show the default value 0, until I requery the table. How to get the latest PKID?
/* Trigger in tblIncident */
CREATE TRIGGER GetPKID
ON tblIncident FOR
INSERT AS
DECLARE @PKID int
DECLARE @NEWVALUE int
DECLARE PKID_Cursor CURSOR FOR SELECT tblIncident.PKID FROM tblIncident, inserted
where tblIncident.PKID = Inserted.PKID
OPEN PKID_Cursor
BEGIN
FETCH NEXT FROM PKID_Cursor INTO @PKID
WHILE (@@fetch_status = 0)
BEGIN
SET @NEWVALUE = 0
/*Call stored procedure - getmaxid to get the latest PKID */
EXECUTE GetMaxId "IN", @NEWVALUE OUTPUT
Update tblIncident SET PKID = @NEWVALUE WHERE PKID = @PKID
FETCH NEXT FROM PKID_Cursor INTO @PKID
END
END
CLOSE PKID_Cursor
DEALLOCATE PKID_Cursor
This question may be a little complicated.
I am building a DTS Package that is moving data from our webstore (written in house) to a Warehouse Management System(WMS - Turnkey) and I've encountered a problem. both pieces of software have an orders table and an Ordered_Items table, related by the order_ID (makes sense so far). Here is the problem. The primary key on the webstore's Ordered_Items table is a single column (basically an Identity variable), while the primary key on the WMS's Ordered_Items table is a dual column primary key, between the Order_ID and the Order_LineID, so the data should be stored like:
OrderID Order_LineID
1 1
2 1
2 2
2 3
3 1
3 2
4 1
Get the Idea? So I have to create this new Order_LineID column. How can I accomplish this with a SQL statement?
Thanks!!!!!
I created table and also I define the primary key its okay
but when I generate the SQL script for that table its not
creating the primary key
CREATE TABLE [dbo].[table1] (
[emp_id] [int] NOT NULL ,
[emp_name] [char] (25) NULL ,
[emp_address] [nvarchar] (50) NULL
) ON [PRIMARY]
GO
PS: I want to use emp_id as primary key but its not defined in the sql script
Thankx a lot
Hello everyone!
Even though I am not "new to SQL Server", my experience in working with it has ebbed and flowed, and working with SQL Server is not something that happens for me even on a monthly basis.
I work as an in-house programmer for a financial investment company and I have been asked to work on a database that is to hold financial data.
One of the requirements for the tables has been that the Primary Key should be [name_of_the_table] + [Identity].
So, for example, for table "tblEquities", the values of the Primary Key would be:
PK
-------
tblEquities1
tblEquities2
tblEquities3
tblEquities4
tblEquities5
My questions are:
1) Is it possible to achieve this by using just one column, as in defining a formula for the "Computed Column Specification" , something that would look like "'tblEquities' + Identity()"?
2) If the above is not possible, I assume it's much easier to just define a column as being Identity column and then create a 2nd computed column that would aggregate the name of the table with the value in the Identity column, so that I end up with a column holding the desired values? (I guess it would look something like
"'tblEquities' + [Identity]")
Also, I have been asked to find out what SQL 2005 have/offers, in term of guaranteeing referential integrity. Are the "Database Diagram", as well as the "Relationships" dialog box, the tools that would meet that criteria or does SQL 2005 contain other tools that would help in this regard?
Thank you!
Hi,
I have created a very simple table. Here is the script:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IndexTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[IndexTable]
GO
CREATE TABLE [dbo].[IndexTable] (
[Id] [int] NOT NULL ,
[Code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [CusteredOnCode] ON [dbo].[IndexTable]([Id]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[IndexTable] ADD
CONSTRAINT [PrimaryKeyOnId] PRIMARY KEY NONCLUSTERED
(
[Id]
) ON [PRIMARY]
GO
The records that i added are:
Id Code
1 a
2 b
3 aa
4 bb
Now when i query like
Select * from IndexTable
I expect the results as:
Id Code
1 a
3 aa
2 b
4 bb
as i have the clustered index on column Code.
But i m getting the results as:
Id Code
1 a
2 b
3 aa
4 bb
as per the primary key order that is a non clustered index.
Can anyone explain why it is happening?
Thanks
Nitin
Hello,
I am trying to recreate a primary key that I dropped in a table....
I'm using a statement like
ALTER TABLE temp WITH NOCHECK add CONSTRAINT PK__tempkey PRIMARY KEY CLUSTERED
(
num,
store
)
But apparently the "store" column was created without a "not null" and it let it create the PK during the table creation but now it won't let me add the PK with that nullable column..
Does anyone know how to get it to use that column to create a primary key after the initial table creation?
Here is the error i get
Cannot define PRIMARY KEY constraint on nullable column in table
Thanks a lot
Hi,
via VBScript, I am inserting data into one table as below:
Code:
conn.Source = "INSERT INTO img (imageDesc,imageName,imageDate,imageUser,imageIP) VALUES ('"& ni &"','"& fn &"','"& Now() &"',"& Session("MM_UserID") &",'"& Request.ServerVariables("REMOTE_HOST") &"')"
In another table, I want to insert the primary key (imageID) of this newly inserted row into a new table called "t_image_Site" along with another value in another column.
Any advice/tutorials... this can be done with a trigger if I'm not mistaken?
JJ
I have a varchar column in a table, which is an unique key column. Now when I design this table should I use this column as a primary key or should I add one more Integer column as a primary key column for the performance?
One more point is, when ever I do a search on this table I will search based on that unique varchar column values only, so even then if I add new integer column just for Primary key I will not use this column for searching values.
With these information, can some body help me in deciding this?
This is a fairly simple question, but what is the easiest way to:create a new numbered column (where value is simply the row number) inan existing table and setting it as a primary key?
View 1 Replies View RelatedI currently have a website which is using ASP.NET 2.0, C#, and SQLServer 2005. The website will be used to enter grants for auniversity. When a new grant is entered, I need to generate a primarykey. The primary key will need to follow the format: Two digit forfiscal year, then number of the grant for that year. Example:Year 08 and 14th grant of the year would be: 0814How can I implement this. Right now, I have a "New Grant.aspx" pagewith a Submit button. I am guessing the date is going to be formattedin C#. How can I check what the last primary key in the database is?Also, it seems to me that SQL Server insists that the primary key be32 bits long, however my primary key will only be 4. How can Ioverride this? Thanks.
View 3 Replies View RelatedHey everyone,
I have just started getting into to SQL and am completely brand new to the whole concepts of relational databases. Someone on this forum pointed to the MSDN videos on LEARNVISUALSTUDIO.NET which have been very helpful. Unfortunately while learning about relational databases and looking at the program that I want to design and make using them, I have run into a pretty big wall, concerning the primary key and foreign key.
For my program, I am trying to save an object, and lets say the base class is SLIDE. Now SLIDE will store basically most of the information that I will ever want to store, such as timeCreated and mainText and slideID(primarykey). But there are other classes that derive from slide that will store just a bit more information than that because of what they are. Lets say there is a class derived from SLIDE called PERSON that stores its parentNode, which is to say something that this slide specifically belongs to and has a reference to. Now the tricky part is that in this program, every single slide can have a reference to another slide, that you would see displayed and that you could just click on and view if you wanted to.
Now relating what I just told about the classes in my program to a relation database standpoint is what confuses me. If I make a table SLIDE, it would hold incomplete data about the PERSON object, because that object has more data than SLIDE has. The answer to this was to make another table called PERSON, which would have more columns. But now we arrive at the big problem: The primary key called maybe SLIDEID would be different in this new PERSON table than in the other table called SLIDE (or any other derived class). Therefore the link between these tables is messed up. In my object orientated mind I am thinking of static class variables that would still stay constant for derived classes, so that when I make a PERSON slide it will increment off of the primary key of the SLIDE table. In other words, is there some sort of super TABLE that you can derive from, like an abstract class, where the primary keys of other tables can build off of because they will be used as the same type of reference to eachother.
If none of this made sense to the reader, I am greatly sorry. I do not really know what else I can say to convey to you the problem I have. Maybe its because I am so used to object orientated languages that this is making it so difficult to explain. If however you do understand what I am talking about, please think about it and help me find a solution to this problem. I am not an experienced programmer, but I do very much enjoy it and I am very excited about starting to make this program, and I have learned that before I start coding it is very important to have a very firm design in mind first.
Thank your for reading,
Jeremy
I would like to get the primary key values in a DML CREATE TRIGGER as follows:
CustomerID = 12 AND InvoiceID = 50
I create the DML trigger for UPDATE and DELETE only, how do I retrieve the values as above ?
Jon Galloway provided a sample in http://weblogs.asp.net/jgalloway/archive/2008/01.aspx but the Primary Key Field and Primary Key Value were inserted into separate columns while I need to combine the Primary Key Field and Primary Key Value into 1 single field, using his example, I want to form the primary key as:
ScoreId = 423
ScoreId = 3064
etc.
Note that his example only show a single primary key in a table whereas I need to handle multiple primary keys in a table.
Hi All,
I am using a trigger. I want to get the data of a row before updating inside this trigger and insert it into a backup table. Please anybody help me. Example with code is highly appreciated.
Thanks in advance.
Hi,
I am trying to execute following sql script in sql-server 2000 query analyzer
CREATE TABLE user_courses (user_id varchar(30) NOT NULL PRIMARY KEY,
course_id varchar(10) NOT NULL PRIMARY KEY)
Its give's me following error :-
Cannot specify multiple primary key constraint
Hence I am not able to ceate table with multiple primary keys. So can any one tell me how to get this done?.
Secondly, Primary key must be unique i.e duplicate values are not allowed in P.K field. But in this case since I am declaring two fileds as primary keys.
Will it allow me to have following records in the user_courses table?
user_id(P.K) course_id(P.K)
bob CRS235
alice CRS235
Tim CRS235
tom CRS635
So, if we consider both the fields as primary keys together than I am not voilating Uniqueness constraint. But, if I look at course_id alone then I am voilating uniqeness property?
Thanks,
Discuss the following sql query with respect to performance in an applicaiton involving more number of concurrent users creating and deleting records. The objective is to create continuous primary key integer values.
Table name: SitePage
Column DataType
--------- -----------
PageID BigINT
PageName nchar(10)
Query to insert new record
DECLARE @intFlag INT
SET @intFlag = 0
WHILE (@intFlag =0)
BEGIN
BEGIN TRY
[Code] ....
We don't want to use auto increment integer value for primary key because of the following reason
[URL] .....
We also don't want to use SEQUENCE as we have to create 50 sequence for 50 tables
We can't do trace flag 272
Environment: Microsoft SQL Server Standard Edition (64-bit), 10.0.5520.0
I was doing a code review for another developer and came across this code:
CREATE TABLE dbo.#ABC
(
ReportRunTime DATETIME
,SourceID VARCHAR(3)
,VisitID VARCHAR(30)
,BaseID VARCHAR(25)
[Code] ....
This EXECUTES with no error or warning message.However, if I change this to CREATE the PK in an ALTER TABLE statement, I get the (expected by me) error:
CREATE TABLE dbo.#ABC
(
ReportRunTime DATETIME
,SourceID VARCHAR(3)
,VisitID VARCHAR(30)
,BaseID VARCHAR(25)
,OccurrenceSeqID INT
[code]...
==> Msg 8111, Level 16, State 1, Line 17 Cannot define PRIMARY KEY constraint on nullable column in table '#ABC'.
==> Msg 1750, Level 16, State 0, Line 17 Could not create constraint. See previous errors.
(note: As the #ABC table is an actual copy of a few of the columns in a "permanent" table, I will likely change the definition as follows such that the columns are defined to match the names / datatypes / NULLability:
SELECT TOP 0
CAST('01-01-1980' AS DATETIME) AS [ReportRunTime]
,SourceID
,VisitID
,BaseID
,OccurrenceSeqID
[Code] .....
Please help me somebody solve my problem with my first :o trigger: ALTER TRIGGER partner_update ON dbo.partner FOR UPDATE AS INSERT INTO partner (name) SELECT name FROM deleted UPDATE invoice SET id_partner= *** WHERE id_partner = (SELECT id_partner FROM deleted) *** - here I want to add a "reference" to the newly added record's automatically generated primary key (not to the updated!) Is it possible?
View 1 Replies View RelatedHello,
Anyone know how to create an update trigger where the primary key isn't fixed.
If the primary key change how can I tie together the Deleted and Inserted tables if more than one row is updated?
/Patrik
I am writing a trigger for getting values to auditlog table when the values gets updated. Below is the code of my trigger.
CREATE TRIGGER [dbo].[Update_Temp] ON [dbo].[Temptable1] FOR UPDATE
AS
DECLARE @bit INT ,
@field INT ,
@maxfield INT ,
@char INT ,
@fieldname VARCHAR(128) ,
@TableName VARCHAR(128) ,
[Code] ....
The code is working fine when the table has primary key associated. However due to some restrictions I will not be able to have a primary key for some tables. I want to implement the same trigger in those tables too. When there is primary key, that primary key needs to get inserted into the audit table and if there is no primary key, i want a specific column value to get inserted instead of the primary key value into the audit table.
For example, i have a student table in which there is a student id, name, dob. there is no primary key defined for the table. So when i update the name or dob, i need the student id to get inserted into the Pk column of the audit table.
I tried modifying the code by checking the @pkcols for Null and if its null to get the old value as the primary key however I was not able to do it .
I want to create a trigger in SQL 2000 Enterprise, but not sure about triggers, how they work, etc. I just know that I was told I could create a trigger when new info is added to one table and check another table to see if this info is already in the other table, if not, copy the data to the other table too.
Would this be what a trigger can do for me?
what would be the best place to learn how triggers work, how to write one and install it, test it, etc?
I have 3 tables on my db, Projects, ProljectAllocationLog and Users
Project consists of Projectid(PK), ProjectName, UserID
ProjectAllocationLog consists of ProjectAllocationID(PK), Projectid, UserID,Date
Users consists of UserID (PK), Fullname
Over the course of time the user allocated to a project may change. The db has been set up to always show the most current user in the UserID of the Projects table,
I want to create a log that will record everytime that user has changed. (ProjectAllocationLog)
Having read through some examples posted on Forums, I believe that I can do this with a trigger, but I am not sure if I am doing it right, the trigger I have written is
Create Trigger tr_UpdateAllocationLog
ON Projects
AFTER Update
AS
If NOT UPDATE (Userid)
DECLARE @PROJECTID
DECLARE @NEWUSER
DECLARE @PREVIOUSUSER
SET @PROJECTID= (SELECT projected FROM Inserted)
SET @NEWUSER = (SELECT UserID from Inserted)
SET @ PREVIOUSUSER = (SELECT UserID from Deleted)
If @NEWUSER <> @PREVIOUSUSER
INSERT INTO ProjectAllocationLog (ProjectID, UserID, Date) VALUES (@PROJECTID, @NEWUSER, GETDATE())
Go
I would appreciate any comments
Hello,
I do have one table "ABC" in DB "Master" & other table "XYZ" in DB "Test".
Both tables are having same structures & same data currently. Now i want to create trigger in such a way that after every insertion & updation on table "ABC" in DB "Master" will insert & update records in table "XYZ" in DB "Test" respectively.
Can any one help me out?
Thanks
Prashant Hirani
Where Dreams Never Ends
I need help writing an Update trigger that puts the current date in a changed record. I understand the basic idea but can't seem to get it to work. Any help would be greatly appreciated
View 1 Replies View Relatedi am trying to create a trigger which when I insert a "y" on a student table in insCheck column, instructor table will create a record of the same person.
The reason is there are two tables are in my DB, student and instructor table. Student can be a instructor so whenever insCheck conlum in studnent table is checked as "y", instructor table should be populated.
Both table studentId and instructorId inserted as manually so that I am not sure how i should populate the instructor's table just fName, mI, and lName and I have go back to the instructor table and populate the rest of the table manually or is there any way to poppulate the insturctorid also while trigger is populate the name onthe instructor table.
My Two tables DDL are like down below
create table student(
studentId char(4) not null primary key,
fName varchar(25) not null,
mI char(1) null,
lName varchar(25) not null,
insCheck char(1) not null,
);
create table instructor(
instructorId char(4) not null primary key,
fName varchar(25) not null,
mI char(1) null,
lName varchar(25) not null,
instructorQual varchar(100) not null,
);
thanks for your help in advance!
gazawaymy
There are 2 tables: OrderID and Order_Details
CREATE TABLE Orders
(OrderID int
Constraint Order_ID_pk primary key
, CustomerID nchar (5)
, EmployeeID int
, OrderDate datetime
, RequiredDate datetime
, ShippedDate datetime
, ShipVia int
, Freight money
, ShipName nvarchar (40)
, ShipAddress nvarchar (60)
, ShipCity nvarchar (15)
, ShipRegion nvarchar (15)
, ShipPostalCode nvarchar (10)
, ShipCountry nvarchar (15)
)
CREATE TABLE Order_Details
(OrderID int NOT NULL
, ProductID int NOT NULL
, UnitPrice money NOT NULL
, Quantity smallint NOT NULL
, Discount real NOT NULL
, constraint Order_Details_PK
Primary key (OrderID, ProductID)
)
Create a Transact SQL procedure, customer_activity, that would, for a given CustomerID, return the number of orders that customer has made, average amount of all the customer orders, and the maximum customer’s order. The CustomerID should be the stored procedure’s input parameter. The stored procedure should use the view customer_orders.
what kind of code to create this? it says i need to use a view customer_orders, which i made:
create view customer_orders as
select orders.orderID, customerID, sum(order_details.orderID) as orderamount
from orders, order_details where orders.orderID=order_details.orderID
group by orders.orderID, customerID
but i don't know how to do it.
Next: the trigger:
Write a trigger that would, for any order entered (inserted), print the order amount as well as the customer’s average and maximum order so far, by using the view and the stored procedures created in this lab.
please and thank you
I have problem with Triggers,Iv never done it before except @school!!
One of our clients Server has same database names(WeighBridge) but Different Instances(1got Express and Other3 have SQL2005).There is a weighbridge scale on SQL Express Database.
I want to create a Trigger that Automatically updates everytime there is weighbridge scale In other Databases that have SQL2005.
If someone can help please a code or tell me what to do,
Create a Trigger on a Table ot Database??
Please Help!!!!!!