Gettting Primary Key Values In A DML CREATE TRIGGER

Jun 10, 2008

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.

 

View 4 Replies


ADVERTISEMENT

Can DTS Automatically Create Primary Key Values Upon Export?

May 5, 2004

I have two practice tables I have created and want to export the values of one into the source table. I want to know if I can export into a table and have the destination table automatically give a primary key value to a record? I haven't been able to figure this out even after fiddling with the "Enable identity insert" checkbox under the Column Mappings tab. I have created source tables with and without primary keys and neither works because of the fact that I need to have a value for a primary key in order to INSERT into the destination.

Do I have to copy the source records into a staging table and assign the PK values myself by hand? This can't be the answer.

ddave

View 2 Replies View Related

SQL 2012 :: Create Random Alphanumeric Characters For Primary Key Values

Feb 11, 2015

For a new project. I need to create random alphanumeric characters as primary key values when inserting a record.

eg: cmSbXsFE3l8

It can start from 4 digit characters and can grow to 6, 7 as required

The database will involve more than 10000 concurrent users.

I don't want to use guid or auto increment integer or sequence.

View 9 Replies View Related

Create Trigger To Check Values At Insert/update

Feb 10, 2004

I have never used triggers before and I have tried to solve one problem. If I have the column "currency" in a table and want to make sure that the entered value i valid in relation to another table that contains valid currency formats, I did it like this:

---------------------------------
CREATE TRIGGER [trigger_checkCurrency] ON [dbo].[Client]
FOR INSERT, UPDATE
AS
declare @currency as char(50)
declare @country as char(50)

declare cur cursor for SELECT currency, country
FROMinserted

OPEN cur
fetch cur into @currency, @country
WHILE @@FETCH_STATUS = 0
BEGIN
if not exists(select * from listinfoid where listname = 'currency' and listid = @currency)
begin
set @currency = (cast(@currency as varchar (3)) + ' is not a valid currency')
CLOSE cur
DEALLOCATE cur
RAISERROR (@currency,16,-1) with log
return
end
if not exists(select * from listinfoid where listname = 'country' and listid = @country)
begin
set @country = (cast(@country as varchar (3)) + ' is not a valid contry')
CLOSE cur
DEALLOCATE cur
RAISERROR (@country,16,-1) with log
return
end
else
fetch cur into @currency, @country

END
CLOSE cur
DEALLOCATE cur
update Client set currency = UPPER(currency), country = UPPER(country)
---------------------------------



I use a cursor to handle multiple rows in an update query.
(SQL2000-server)

Is there an easier och better way to do this?
I´m a bit unsure of this code.

Thanx!

/Erik

View 2 Replies View Related

Transact SQL :: Getting Primary Key Values As A List Of Values

Oct 14, 2015

I am trying to audit data quality based on some defined data quality rules. The rules are stored in tables and processed using stored procedures. I am facing a problem while generating audits. Let's say I am trying to audit data in OrderDetail table. The table design is mentioned below...I inserted some sample data into the table using RedGate data generator.The audit table output I am expecting is as mentioned in the screenshot below

Its the PrimaryKeyAttributeValues column I am facing problems with. I am using STUFF function within a dynamic SQL query to get the primary key's as a list of comma separated values.

View 2 Replies View Related

Gettting A Job To Fail

May 19, 2008

I'm trying to get a job to fail using a stored procedure command.

I have been using:
EXEC sp_stop_job @job_name = 'Archive Tables'

But this command only cancels the job, I want to the job to report back a failure.

Is there a sp_fail_job, or a sp_quit_job or something?

Thanks in advance

View 14 Replies View Related

ASP.NET,Gettting Error In The Program

Jan 4, 2007

Hi,
please have look into the code and let me know the solution plz. 
string ProID;
try
{
using(SqlConnection conn=new SqlConnection(source))
{
conn.Open();
DataSet ds=new DataSet();
DDLProject.Items.Clear();
SqlCommand cmd=new SqlCommand("SP_ProjectSelect",conn);
cmd.CommandType=CommandType.StoredProcedure;
cmd.Parameters.Add("Name",SqlDbType.NChar,30,"@Name");
cmd.UpdatedRowSource=UpdateRowSource.None;
cmd.Parameters["Name"].Value=DDLProductLine.SelectedItem;
 
SqlDataReader dr=cmd.ExecuteReader(); ------------->>>> Here iam getting Following Error.
 
while(dr.Read())
{ Response.Write(dr["ID"].ToString());
}
 
}
}
catch(System.Exception ex)
{
Response.Write(ex);
}
Error:
System.InvalidCastException: Object must implement IConvertible. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteReader() at MIS.UI.ResourceList.DDLProductLine_SelectedIndexChanged(Object sender, EventArgs e) in c:inetpubwwwrootdotnet pgmsmisui
esourcelist.aspx.cs:line 121 
Any suggestion plzz,where went wrong.
Thanks in advance
Regards
Mahesh Reddy 
 
 

View 2 Replies View Related

Gettting Last Inserted Record

Feb 9, 2007

hi,i need to select last inserted record in my table,can any one show some example query for that please

View 6 Replies View Related

Use Trigger To Generate Primary Key

Jan 27, 2004

I 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

View 5 Replies View Related

Creating A Primary Key In A Trigger

Sep 17, 2007



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.

View 3 Replies View Related

Gettting Time Of SQL Server Computer

Jul 20, 2005

Hi everyone,Is there a system stored procedure or another way that I can retrievethe current date and time of the SQL Server computer?Any info would be appreciated...Thanks in advance,Dan

View 1 Replies View Related

Boolean: {[If [table With This Name] Already Exists In [this Sql Database] Then [ Don't Create Another One] Else [create It And Populate It With These Values]}

May 20, 2008

the subject pretty much says it all, I want to be able to do the following in in VB.net code):
 
{[If [table with this name] already exists [in this sql database] then [ don't create another one] else [create it and populate it with these values]}
 
How would I do this?

View 3 Replies View Related

Trigger : Insert Maximum Primary ID

Mar 15, 2005

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

View 4 Replies View Related

How To Create New CLR Trigger From Existing T-Sql Trigger

Mar 18, 2008

how to create new CLR trigger from existing T-Sql Trigger Thanks  in advance

View 3 Replies View Related

Getting Primary Key Of The Updated Row In A Table Inside Trigger

May 16, 2008

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.

View 3 Replies View Related

How To Reference The Primary Key Of A Newly Added Record In Trigger?

Jun 24, 2004

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 Related

Update Trigger, Multiple Rows, Non Static Primary Key

Jul 17, 2007

Hello,

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

View 9 Replies View Related

SQL Server 2012 :: Trigger For Update Action Without Primary Key

Jun 18, 2015

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 .

View 1 Replies View Related

Create A Primary Key

May 28, 2002

l would like to run a health check on my newly designed database to ensure that l've covered all the necessary points to ensure that l have a good database. Is there there such a procedure?

If l want to create a primary key on a table with duplicates, how would l achive that? l tried to create a new table then create the pk. then ran the following script to populate

instert into tabB
select * distinct tabA

It did not work. Please advive

View 1 Replies View Related

How Do I Concatenate Different Values For The Same Primary Key Without Using Cursor..

Mar 21, 2002

Can I avoid cursor (use while of if statement instead ) if for example I have two sales people for the same company and I need one row per company?

View 1 Replies View Related

Choosing Values For Primary Keys

Jul 20, 2005

Hello group:I've done alot of reading on this subject somewhat and have found thatmany people have many different opinions on this subject. My questioncenters mainly around using a lookup table to enable users to select apre-defined list of values.I have developed a practice myself of avoiding AutoNumber type datafields for primary keys where the primary key will be related to achild table. Nevertheless, what do most users do with lookup tables?My thoughts are to create a small key value for each value in thelookup table. For example:I might have a Carriers table which shows a list of carriers that Imight ship an order by. One of the entries may be 'Air Freight -Overnight', or 'Air Freight - 2nd Day Air'. I've seen a few exampleswhere the primary key field for each entry like these would beautonumber, or at least, a numeric value. What I like to do is createmy own key, like for 'Air Freight - Overnight', I might use 'AFO' forthe key, and for 'Air Freight - 2nd Day Air', I might use 'AF2'. Anythoughts on this? Mine are that even tho the users may never see thisvalue - I, as the developer will see it and I tend to prefer a keyvalue based on real data that means something other than anauto-incremented number. In referencing the well-known Northwind.mdbdatabase, I noticed their Categories table used a number field value,like 1, 2, 3....etc, but their customers table used values like'ALFKI' to represent their key values.What are some other thoughts out there? I'm working with Accesscurrently, but this project is about to move to SQL Server.James

View 3 Replies View Related

Insert Values In Primary Key Field

May 15, 2006

Hi,

Does anyone know how should I create a table in order that I can insert values(numbers) in the primary key field, using insert statements. I also would like to know if there are any differences between SQL 2k and SQL 2k5.

Thanks in advance for any reply.

View 1 Replies View Related

In CREATE DATABASE What Is PRIMARY?

May 23, 2006

Doing the exercises in a book, which starts CREATE DATABASE xyz ON PRIMARY...
 
It fails, so stepping through it.  What is PRIMARY?
Searching Help seems always to give primary KEY, but cannot be that at point of creating the DB.
XP ProVS Pro 2005SQL Server 2005 Dev.

View 2 Replies View Related

Set Name Of Primary Key In The CREATE TABLE

Apr 28, 2008

I think there has to be a way to do this but I'm not seeing it.

I would like to set the names of my primary keys in the CREATE TABLE statements. I like this for documentation so it's very clear what the PK name is. When the system generates the key names, it always add the number suffix at the end. I would need to do this both when the PK is a single column and when it is multiple columsn (see examples below).

Thanks very much for your assistance.


CREATE TABLE dbo.SecAppRole1 (

app_id INT IDENTITY(1,1),

app_name_field VARCHAR(128) NOT NULL PRIMARY KEY ,

app_role VARCHAR(128) NOT NULL,

app_role_password VARCHAR(50) NOT NULL)


CREATE TABLE dbo.SecUserAppPermission1 (

app_id INT NOT NULL,

windows_user_name VARCHAR(128) NOT NULL,

user_permission CHAR(01) NOT NULL,

PRIMARY KEY CLUSTERED (app_id ASC, windows_user_name ASC))

View 4 Replies View Related

Primary Key Means It Should Not Accept Null Values Is It?

Sep 15, 2004

Friends,

I am inserting a value from vb.net to sql server 7.
i created a table in the sqlserver with primary key.
In VB.net form i didnt check the values of the check box.
when i try to save the empty or null the sql server should throw the error. but its accepting the null value.
how is it possible?
can anyone help me?
FYI:
table name: JobMast
Fields:
jobcode int primary key
jobtitle varchar(10)

View 12 Replies View Related

Missing Identity Values In Primary Keys

May 29, 2012

We are facing the following issue, several machines/users that are executing very often a command similar to :

INSERT INTO TableName (FieldOne,FieldTwo) VALUES ('ValueOne','ValueTwo');
SELECT SCOPE_IDENTITY() AS Table_ID;

Where TableName has a primary key defined as identity(1,1).and that Table_ID is being used as reference in others tables

These queries are executed using different dababase users and among several diffrent apps..The Problem is that we are detecting lost block of "Table_ID's" as the other tables shows the InsertedID as a reference, but the TableName table lacks of this ID record. In other words, the INSERT seems to work, the SCOPE_Identity returns an InsertedID, and the other tables are populated using this number. However, when we query the TableName table the mentioned record does not exist. We are profiling the server and we're sure that there are no DELETE statement on the TableName table. This seems to be happening when the are either deadlocks or blocked processes. Whenever the deadlocks and locks disappear/solved, everything works as expected.why the Scope_Identity returns the Inserted ID if the INSERT action had failed.

View 4 Replies View Related

Assigning Automatic Values To Primary Keys

Aug 10, 2007

what other method can you use to generate primary keys automatically. My boss disagrees with me using identity for the primary keys. He says it is not professional. I am working on SQL Server 2005 platform. Can anybody advice me?

View 3 Replies View Related

SQL Db Create Tables And Cluster Primary Key

Jun 21, 2005

With the last table being created below, it has a clustered primary key.One of the fields it is referencing on the previous table, courseId, can NOT be unique.But without it being unique, the cluster primary key won't work.Is there another way to achieve what I am trying to do here?CREATE TABLE dbo.courseScores   (          courseId varchar(20) NOT NULL     CONSTRAINT FK_courseId_courseStructure2 FOREIGN KEY (courseId)    REFERENCES courseStructure (courseId), 
          studentId varchar(20) NOT NULL    CONSTRAINT FK_studentId_students2 FOREIGN KEY (studentId)    REFERENCES students (studentId), 
     CONSTRAINT PK_courseScore PRIMARY KEY CLUSTERED (courseId, studentId)
          )
 
CREATE TABLE dbo.objScores  (             objID varchar(20) NOT NULL    CONSTRAINT FK_objId_objstructure FOREIGN KEY (objID)    REFERENCES objStructure (objID), 
    studentId varchar(20) NOT NULL    CONSTRAINT FK_studentId_students3 FOREIGN KEY (studentId)    REFERENCES students (studentId), 
    courseId varchar(20) NOT NULL    CONSTRAINT FK_course FOREIGN KEY (courseId)    REFERENCES courseScores  (courseId),                     CONSTRAINT PK_objScores PRIMARY KEY CLUSTERED (objID, studentId, courseId)           )
Thanks all,Zath

View 1 Replies View Related

Create Primary Key With Increment And Format?

Nov 19, 2005

I have an access table that has a primary key (entitled "ID Number"), no duplicates, the field is an integer.And, importantly, the value is set to "increment".The format is "phd"000 -  so it starts out phd001, phd002, and so on...How to do this in an SQL table?  Can that format be done?  Or is it better not to do it via SQL but in coding instead?

View 1 Replies View Related

Create A Table With A Union And Specify Primary Key

Jul 7, 2004

I want to create a table with a union. Which I have already accomplished. I want to specify the Primary Key in the statement.

Or would I have to use another statement. How would I do that? With an update and what would the syntax be?



Thanks before hand,

itarin

View 1 Replies View Related

Create Table + Index + Primary

Dec 17, 2006

for MS SQL 2000
how can I do this in one time (into the CREATE TABLE)

CREATE TABLE [dbo].[Users] (
[id_Users] [int] NOT NULL ,
[Name] [nvarchar] (100) NULL,
[Serial] [nvarchar] (100) NULL,
) ON [PRIMARY]

ALTER TABLE [dbo].[Users] WITH NOCHECK ADD
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[id_Users]
) ON [PRIMARY]


CREATE UNIQUE INDEX [IX_Users] ON [Users]([Serial]) ON [PRIMARY]

and that one

CREATE TABLE [dbo].[UsersExtra] (
[id_Users] [int] NOT NULL
) ON [PRIMARY]


ALTER TABLE [dbo].[UsersExtra] ADD
CONSTRAINT [FK_UsersExtra_Users] FOREIGN KEY
(
[id_Users]
) REFERENCES [Users] (
[id_Users]
) ON DELETE CASCADE


thank you

View 6 Replies View Related

Removing Duplicates To Create A Primary Key

Jan 3, 2008

Hi, I'm in the midst of an Access 2003 to SQL server 2000 upsizing project and have come across a table on Sql Server that has a field that looks like it's supposed to be the PK but it contains duplicates. What I'd like to do is to have a cursor start at the first value and increment the next value by 1. Could someone explain how I'd go about this?

Many thanks,
Peter

View 6 Replies View Related

Create Foreign Key Using Composite Primary Key

Jul 24, 2014

I am trying to create a FK using a composite PK and here are the details that I want to achieve.

Table -A
Column A1 not null,
Column A2 not null
Primary key (A1, A2).

Table -B
Column B1 Primary key.
Column B2 not null
FK (B2) References A(A1).

When I try to do this I am getting some errors. Questions: First of all is this possible? if yes, then how I can create it.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved