inserting a new record

I want to insert a new record into my db in asp.net. Im programming in vb and using an sql server database. i know this is a begginers question but that's exactly what i am. Thanks in advanceHave you had a look at the ASP.net starter kits?

Inserting a Control Record into a Flat Text File through SSIS

I am working on an SSIS project where I create two flat files for submission to a data contractor. This contractor requires a control record be the first line in the file. I create the control record based on the table information being exported.

What I would like to know is, is it possible to utilize the Header Section of the Flat File Destination Editor to insert the control record? And, as it is dynamic, what kind of coding must I do in order to utlise this functionality?

Thanks.

Yes, I would guess that you can do exactly this using the header section.

You can set it dynamically by putting an expression on the [<Flat File Destination Name>].[Header] property of the parent data-flow task.

-Jamie

|||

Ok, I see that this can work, but looking at the available variables, functions for expressions, I do not see how I would get the data inserted from another text file (table) already created into this second one.

Truly not trying to be dense here, just "can't seem to see the forest for the trees."

Thanks.

|||

That's a bit of a different requirement. You may be hampered by the fact that the maximum length of the result of an expression can only be 4000 chars

The way to do it would probably be to build the text up programatically in a script task.

-Jamie

sql

inserting 100 records

How to insert 100 record at a time by explicit inserting of identity column i.e.., by setting identity column to false

You mean like:

INSERT INTO t1(c1,c2)

SELECT '1','2'

UNION

SELECT '3','4'

UNION

...

?

|||

This will turn off the identity column for a table,

set identity_insert <tablename> on

[insert 100 records .. ]

set identity_insert <tablename> off

|||

No i mean if identity column is off i.e.., the we should explicitly insert ID column by fetching an XML having 100 records for example

Table1

ID StudRollNo StudName

Inserting into table1(Identity column for column ID is OFF) where i will get the XML of table having 100 records like

ID StudRollNo Studname

|||If you mean to read data from XML into datbase table,?I?suggest?you?learn?XQuery?in?SQL2005

InsertCommand using data from a second SqlDataSource - ASP.NET 2.0

I have a process that inserts a new record using the InsertCommand of aSqlDataSource. As part of the process, I need to insert data the is available in a different SqlDataSource. I was trying this with the Insert Parameter:

<asp:FormParameterName="Change_Title"FormField="Change_Title"/>

where Change_Title is available on screen. Doesn't work. Is this possible?

HI

Can you see if this post helps or gives you some idea of how to achieve it.

http://forums.asp.net/p/1124558/1766373.aspx#1766373

The post though gives a way to avoid the need for two SQLDatasources but use one to handle both level updates.

Hope this helps.

VJ

insert/update trigger

Tbl1 inserts 1 record(with some fields populated) in tbl2. then I need get values from tbl3 to populate the rest of the fields in tbl2(update the record).
tbl1 = tblallBag_data
tbl2 = tblBag_data
tbl3 = tblShipping_sched

I created a trigger in tbl1 to insert a record into tbl2 and it works fine.

CREATE TRIGGER trgtblBag_Data ON dbo.tbltblallBag_data
FOR INSERT
AS

INSERT INTO tblBag_data (work_ord_num, work_ord_line_num, bag_num, bag_scanned_by, bag_date_scanned, bag_quantity)
SELECT work_ord_num, work_ord_line_num, bag_num, bag_scanned_by, bag_date_scanned, bag_quantity
FROM inserted

How can I update tbl2?
Should I create another trigger to update tbl2?
Should I join the two tbls(tbl2 & tbl3) to find
@.work_ord_num = work_ord_num , @.work_ord_line_num = work_ord_line_num

Thanks for your help!tbl2 and tbl3 should be joined with inserted.

insert/update timestamp in a SQL server 2000 db programatically

Hi,

How can i store the record insert/update timestamp in a SQL server 2000 db programacally. ? what are the date/time functions in ASP.NET 2.0 ? I know that this can be done by setting the default valut to getdate() function in SQL, but any other way on ASP page or code-behind page ?

Thanks,

Alex

You can use

string

s =DateTime.Now.ToString("dd/MMM/YYYY");

and then put it into the relevant parameter for your SqlCommand

|||Yes, that is correct if i assume i put that line of code in the code-behind page. But what will the syntax be if i need to use the same in the aspx page

My insert statement is as follows:

InsertCommand="INSERT INTO [StudentRegistration] ([RegDate], [FirstName], [SecondName], [FamilyName], [Photo], [CourseId], [MorningClass], [AfternoonClass], [Block], [Street], [HouseAptNo], [Area], [POBox], [PostalCode],, [HomePhone], [Mobile], [WorkPhone], [BirthDate], [Gender], [Nationality], [MaritalStatus], [CivilIdNo], [ExpiryDate], [ContactName], [ContactTel], [ContactMob], [ContactEmail], [MedicalCond], [CompleteHS], [CompYear], [ExpDate], [WhichSchool], [SchoolType], [Other], [QualTitle1], [QualInst1], [QualComp1], [QualTitle2], [QualInst2], [QualComp2], [QualTitle3], [QualInst3], [QualComp3], [QualTitle4], [QualInst4], [QualComp4], [Notes], [DateAdded], [AddedByFK]) VALUES (@.RegDate, @.FirstName, @.SecondName, @.FamilyName, @.Photo, @.CourseId, @.MorningClass, @.AfternoonClass, @.Block, @.Street, @.HouseAptNo, @.Area, @.POBox, @.PostalCode, @.Email, @.HomePhone, @.Mobile, @.WorkPhone, @.BirthDate, @.Gender, @.Nationality, @.MaritalStatus, @.CivilIdNo, @.ExpiryDate, @.ContactName, @.ContactTel, @.ContactMob, @.ContactEmail, @.MedicalCond, @.CompleteHS, @.CompYear, @.ExpDate, @.WhichSchool, @.SchoolType, @.Other, @.QualTitle1, @.QualInst1, @.QualComp1, @.QualTitle2, @.QualInst2, @.QualComp2, @.QualTitle3, @.QualInst3, @.QualComp3, @.QualTitle4, @.QualInst4, @.QualComp4, @.Notes, @.DateAdded , @.AddedByFK)"

<asp:ParameterName="DateAdded"Type="DateTime"/>

in this code, how do i retrieve the current date/time from SQL server while inserting a new record ?

i want to set the DateAdded field to default to the current date/time

Thanks,

Alex

|||I would personally use a stored procedure as then you can pass back the SQL datetime as the return value or an output parametersql

INSERT WHERE

Is there any Sql Cluase to say insert if the record not found in a single
statement
i.e : INSERT INTO STUDENTSVALUES('RKNET')
where not exists Name='RKNET'
thnaks in advance.
RKNETI made a query using IF clause.
IF NOT EXISTS( SELECT Name FROM STUDENTS WHERE Name='RKNET' )
INSERT INTO STUDENTSVALUES('RKNET')
"RKNET"?? ??? ??:

> Is there any Sql Cluase to say insert if the record not found in a single
> statement
> i.e : INSERT INTO STUDENTSVALUES('RKNET')
> where not exists Name='RKNET'
> thnaks in advance.
> RKNET|||> Is there any Sql Cluase to say insert if the record not found in a single
> statement
Try this
IF NOT EXISTS (SELECT * FROM table WHERE [name]='RKNET')
INSERT INTO STUDENTSVALUES('RKNET')
ELSE
blabla
"RKNET" <RKNET@.discussions.microsoft.com> wrote in message
news:2049DBD3-2B67-4A09-B77D-604CF570D592@.microsoft.com...
> Is there any Sql Cluase to say insert if the record not found in a single
> statement
> i.e : INSERT INTO STUDENTSVALUES('RKNET')
> where not exists Name='RKNET'
> thnaks in advance.
> RKNET

Insert Uniqueidentifier after the fact

I have an existing table that i would like to add a uniquidentifier to
each record of the table. I have already create a column for the
uniqueid. What sql script could I run to actually place a value for
the newly created column for each record?
thanks for your help ahead of timeWhat sql script could I run to actually place a value for

Quote:

Originally Posted by

the newly created column for each record?

You can use NEWID() to backfill existing data:

UPDATE dbo.MyTable
SET MyColumn = NEWID()

--
Hope this helps.

Dan Guzman
SQL Server MVP

<pltaylor3@.gmail.comwrote in message
news:1157114291.688642.258140@.b28g2000cwb.googlegr oups.com...

Quote:

Originally Posted by

>I have an existing table that i would like to add a uniquidentifier to
each record of the table. I have already create a column for the
uniqueid. What sql script could I run to actually place a value for
the newly created column for each record?
thanks for your help ahead of time
>

|||Thats perfect. Thanks for the help.
Dan Guzman wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

What sql script could I run to actually place a value for
the newly created column for each record?

>
You can use NEWID() to backfill existing data:
>
UPDATE dbo.MyTable
SET MyColumn = NEWID()
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
<pltaylor3@.gmail.comwrote in message
news:1157114291.688642.258140@.b28g2000cwb.googlegr oups.com...

Quote:

Originally Posted by

I have an existing table that i would like to add a uniquidentifier to
each record of the table. I have already create a column for the
uniqueid. What sql script could I run to actually place a value for
the newly created column for each record?
thanks for your help ahead of time

Insert Triggers

I have written an Insert Trigger to examine newly inserted records and set some values. However, each time a record is inserted, all records are checked. How can I make the trigger work only on newly inserted records?Within the trigger, you can access a view called INSERTED that shows only the rows that are being inserted by the statement that launched the trigger. You can use the INSERTED view (probably via a JOIN) to limit the number of rows you are affecting in your underlying table.

-PatP|||my telepathic usb port is clogged...can you post the trigger...

probably take us a few minutes...

DDL would be nice as well

and pat's correct(what again? say it ain't so...)|||CREATE TRIGGER CheckWorkflow ON [dbo].[tblGroup]
FOR INSERT
AS
insert into WFTasks (DataRecordId, TaskNum, Status, UserId, StartDateTime)
select tblGroup.Id as DataRecordId,
1 as TaskNum,
"Ready" as Status,
tblUsers.Id as UserId,
getdate() as StartDateTime
from tblGroup, tblUsers, tblVendors where (tblGroup.I_Field3=tblVendors.OdissVendorId)
And (tblGroup.I_Field6 Is Null OR tblGroup.I_Field6='0')
And (tblUsers.WFID=1)

..a little complex. the check for tblGroup.I_Field6 is necessitated because all records are being checked - this where clause could be stripped off if only new records were being checked.|||Something like this would do it:

CREATE TRIGGER CheckWorkflow ON [dbo].[tblGroup]
FOR INSERT
AS
if exists (select 1 from inserted)
insert into WFTasks (DataRecordId, TaskNum, Status, UserId, StartDateTime)
select i.Id, 1, 'Ready', u.Id, getdate()
from inserted i
inner join tblVendors v
on i.I_Field3=v.OdissVendorId
inner join tblUsers u
on (u.WFID=1)|||thanx..will try this.

Insert Trigger to Update table

Hi,

Does anyone know of a simple way to do this? I want to create an
insert trigger for a table and if the record already exists based on
some criteria, I want to update the table with the values that are
passed in via the insert trigger without having to use all the 'set'
statements for each field (so if we add fields in the future I won't
have to update the trigger). In other words, I want the trigger code
to look something like this:

if exists (select * from TableA where Fld1 = inserted.Fld1) then
//don't do insert, do an update instead (would i want to rollback here?
and will I have access to the 'inserted' table still?)
Update TableA
Set TableA.<all the fields> = Inserted.<all the fields>
where Fld1 = inserted.Fld1
end if

Any help or ideas would be appreciated.
Thanks,
TeresaUPDATE requires that you specify the columns by name. It's best
practice to do so in an INSERT statement too.

Always specify the column names. In the long run this will improve
reliability and save you development time.

--
David Portas
SQL Server MVP
--|||takilroy@.yahoo.com wrote:

> Hi,
> Does anyone know of a simple way to do this? I want to create an
> insert trigger for a table and if the record already exists based on
> some criteria, I want to update the table with the values that are
> passed in via the insert trigger without having to use all the 'set'
> statements for each field (so if we add fields in the future I won't
> have to update the trigger). In other words, I want the trigger code
> to look something like this:
> if exists (select * from TableA where Fld1 = inserted.Fld1) then
> //don't do insert, do an update instead (would i want to rollback here?
> and will I have access to the 'inserted' table still?)
> Update TableA
> Set TableA.<all the fields> = Inserted.<all the fields>
> where Fld1 = inserted.Fld1
> end if
> Any help or ideas would be appreciated.
> Thanks,
> Teresa

Nice hack.

A rollback is no good because you'd lose the update as well. But you could
delete the inserted row.

You may also have issues with primary keys and other constraints. If a
constraint fires before the trigger, your insert will fail on a pk
constraint and your clever trigger will never fire.

Finally, the performance issue is real. Doing the insert, deleting it, and
then updating causes only one real write to the table that has to be
committed, but carries three complete journal writes. An update only
carries two discreet write. It might be worthwhile to pump a few million
operations in each combination so you can at least speak knowledgeably
about what the real performance price is.

--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@.(Sec)ure(Dat)a(.com)|||On 12 May 2005 08:50:10 -0700, takilroy@.yahoo.com wrote:

>Hi,
>Does anyone know of a simple way to do this? I want to create an
>insert trigger for a table and if the record already exists based on
>some criteria, I want to update the table with the values that are
>passed in via the insert trigger without having to use all the 'set'
>statements for each field (so if we add fields in the future I won't
>have to update the trigger). In other words, I want the trigger code
>to look something like this:
>if exists (select * from TableA where Fld1 = inserted.Fld1) then
>//don't do insert, do an update instead (would i want to rollback here?
>and will I have access to the 'inserted' table still?)
> Update TableA
> Set TableA.<all the fields> = Inserted.<all the fields>
> where Fld1 = inserted.Fld1
>end if
>Any help or ideas would be appreciated.
>Thanks,
>Teresa

Hi Teresa,

There is no way to avoid listing the columns in an UPDATE statement. If
there were, I'd recommend against it (just as I recommend against using
SELECT * or INSERT without column-list in production code).

Also, your trigger's pseudo-code will do an update for all rows that
were inserted if at least one of them exists in TableA. You could remove
the existance check; the effect will be the same (rows that are not in
TableA won't be changed, rows that are will be - and if no row in
inserted is also in TableA, nothing changes in TableA), but it will
somewhat improve performance.

Also, Kenneth is correct - constraints are checked before the trigger is
executed. The only way around that is to use an INSTEAD OF trigger that
updates rows that are already present and inserts rows that are not yet
present.

If you need help transforming this to an INSTEAD OF trigger, just
holler.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Insert Trigger not getting row data for email body

hello,

need help with a simple trigger i have been working on. the trigger automatically sends me an email out when a record is inserted, how ever i can't seem to get the row column data into the email. The part i do not understand is that I get the row column data information in the email if I update the row.

This is for 2005 SQL

Any direction would be greatly appreaciated

OneIDesigned

Maybe the code for the trigger is incorrect?

|||

Here is the trigger code

<code>

setANSI_NULLSON

setQUOTED_IDENTIFIERON

go

ALTERTRIGGER [dbo].[TestimonalEmailInsert]

ON [dbo].[CMRC_Testimonal]

AFTERINSERT

AS

BEGIN

SETNOCOUNTON;

declare @.TestimonalIDint

declare @.FullNamenvarchar(150)

declare @.FullNameNewnvarchar(150)

declare @.CompanyNamenvarchar(150)

declare @.CompanyNameNewnvarchar(150)

declare @.Emailnvarchar(150)

declare @.EmailNewnvarchar(150)

declare @.Testimonalvarchar(2000)

declare @.TestimonalNewvarchar(2000)

declare @.messagevarchar(2000);

SELECT @.TestimonalID= [dbo].[CMRC_Testimonal].TestimonalID,

@.FullName= [dbo].[CMRC_Testimonal].FullName

FROM [dbo].[CMRC_Testimonal]WHERE [dbo].[CMRC_Testimonal].TestimonalID=(SELECT TestimonalIDFROM Inserted)

Set @.message='Testimonial ID '+ltrim(@.TestimonalID)+' has been updated'+

' previous First Name is '+ @.FullName+' and the new Full Name is '+ @.FullNameNew

EXEC msdb.dbo.sp_send_dbmail

@.profile_name='MSQLMAILProfile',

@.recipients='myemail@.myemail.com,

@.body= @.message,

@.subject='Testimonal has been inserted';

END

</code>

Hope this helps

|||

Do you use a Stored Procedure to do the insert? If so, you can use Visual Studio to step into the procedure and then into the trigger. This will allow you to debug the trigger and see if any of the variables are being set.

Also, you should change your sql statement to use a join to the inserted table.

|||

Basically I found that the problem was related to having <NULL> in some of the rows of column data that I was trying to insert into the body of the email. If one of the colums had a <NULL> the entire body of the email would not show up.

The work around I am currently using is to add default information at the table level so each record will column will have data. "Not Supplied"

I would like to configure the trigger to insert "" into the body if <NULL> exist, but I have not found any code to resolve the issue at the trigger level.

Thanks

|||

OneIdesigned:

The work around I am currently using is to add default information at the table level so each record will column will have data. "Not Supplied"

That isn't really a good idea. Null is different to "Not Supplied" as it means it is an unknown quantity so you should leave it as Null if the user hasn't supplied it.

OneIdesigned:

I would like to configure the trigger to insert "" into the body if <NULL> exist, but I have not found any code to resolve the issue at the trigger level.

Use the IsNull function.

Insert Trigger for Parent/Child

I am having problems creating a trigger in SQL Server? I have 2 tables (parent and child) with one to many relationship. When I save a record, one row gets inserted in the parent and one to many gets inserted in the child. The trigger is on the parent table and it is trying to select the number of new records just inserted in the child table that meets a certain criteria. Since the transaction hasn't been committed I can not select the number of records from the child. Does anyone know how to handle this? My manager insists this be done in a trigger.

Thanks,
James

Where did you put the insert logic to the child table? I mean did you insert 1~N row(s) into child table in the insert trigger of parent table? Anyways I suppose you did like I say, as it helps to matainence the data consistency. Then you can add a column to parent table, which is used to record effected rows in child by the row. And in the insert trigger of parent table, let's declare a INT variable with initial value 0, every time an insert to child table will cause the variable increased by 1. After all required rows have been inserted into child, update the row in parent table with the INT variable. Something like this:

create trigger trg_ins_Parent on tbl_Parent for insert
as
begin
declare @.i int
set @.i=0
while(...)
begin
insert into tbl_Child select val1,val2,...
set @.i=@.i+1
end
update tbl_Parent setChildCnt=@.i
where rowid=inserted.rowid
end
go

Insert trigger changing record

Hello all!
I want to create an insert trigger to change some fields of the inserted
record. I want to put in two fields the system date and system time.
When I try to update Inserte table I get an error telling me I cannot
update inserted tables.
Can anyone give me a hand on this?
CREATE TRIGGER [Transactions_Insert] ON [dbo].[Transactions]
FOR INSERT
AS
update Inserted
set CreationDate = dbo.idlog_date(current_timestamp)
--
Function idlog_date returns the date in my format.
Thanks in advance,
Hugo MadureiraHugo Madureira wrote:
> Hello all!
> I want to create an insert trigger to change some fields of the inserted
> record. I want to put in two fields the system date and system time.
> When I try to update Inserte table I get an error telling me I cannot
> update inserted tables.
> Can anyone give me a hand on this?
>
CREATE TRIGGER [Transactions_Insert] ON [dbo].[Transactions]
FOR INSERT
AS
UPDATE dbo.transactions
SET CreationDate = dbo.idlog_date(current_timestamp)
GO
It seems like overkill to use a trigger for this. Have you considered
declaring a DEFAULT value instead: DEFAULT CURRENT_TIMESTAMP.

> Function idlog_date returns the date in my format.
A DATETIME column doesn't have a "format". Why store the date as
anything other than DATETIME or SMALLDATETIME?
David Portas
SQL Server MVP
--|||Yes, thats right. You have to update the original data which is already
store in there.
UPDATE Transactions
SET CreationDate = dbo.idlog_date(current_timestamp)
FROM Transactions T
INNER JOIN INSERTED I
ON T.<YourprimaryKey> = I.<YourprimaryKey>
HTH, Jens Suessmeyer

Insert Trigger causes new record to disappear

Hello, I am using the following trigger. After I insert a new record a new
record into the table, the record disappears and a previous record will show
up as the last record. If I change it to just an update trigger, it works
fine. My question is why would an insert trigger cause this anomaly?
Thanks, Steven
CREATE TRIGGER tgrBusinessEmployee_i ON dbo.tblBusinessEmployee
FOR INSERT
AS
INSERT into tlogBusinessEmployee
(EmployeeID, BusinessLocation, LastName, FirstName, NickName,
WebPassword, Title, --CurriculumVitae,
ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
yn_ActiveEmployee, yn_PublishToWeb,
yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
LogProcess)
SELECT
Employee_ID, BusinessLocation, LastName, FirstName, NickName,
WebPassword, Title, --Convert(varchar(5000),CurriculumVitae),
ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
yn_ActiveEmployee, yn_PublishToWeb,
yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
'InsertUpdate'
FROM inserted
Steven,
Perhaps the insert into tlogBusinessEmployee inside the trigger is failing,
causing the insert into tblBusinessEmployee to fail.
If you add error handling to the trigger, you can detect an insert error and
debug (through a print statement) what the trigger is actually doing.
Alternatively, you can step through an insert in Query Analyzer or Visual
Studio, and debug the trigger.
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"Steven K0" <stroy@.api.com> wrote in message
news:efT2ywFnEHA.3392@.TK2MSFTNGP15.phx.gbl...
> Hello, I am using the following trigger. After I insert a new record a
new
> record into the table, the record disappears and a previous record will
show
> up as the last record. If I change it to just an update trigger, it works
> fine. My question is why would an insert trigger cause this anomaly?
> Thanks, Steven
> CREATE TRIGGER tgrBusinessEmployee_i ON dbo.tblBusinessEmployee
> FOR INSERT
> AS
> INSERT into tlogBusinessEmployee
> (EmployeeID, BusinessLocation, LastName, FirstName, NickName,
> WebPassword, Title, --CurriculumVitae,
> ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
> yn_ActiveEmployee, yn_PublishToWeb,
> yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
> LogProcess)
> SELECT
> Employee_ID, BusinessLocation, LastName, FirstName, NickName,
> WebPassword, Title, --Convert(varchar(5000),CurriculumVitae),
> ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
> yn_ActiveEmployee, yn_PublishToWeb,
> yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
> 'InsertUpdate'
> FROM inserted
>
|||Sounds like a problem with the client code. Are you using an identity
column as the PK for this table? Does the log table also have an identity
column?
"Steven K0" <stroy@.api.com> wrote in message
news:efT2ywFnEHA.3392@.TK2MSFTNGP15.phx.gbl...
> Hello, I am using the following trigger. After I insert a new record a
new
> record into the table, the record disappears and a previous record will
show
> up as the last record. If I change it to just an update trigger, it works
> fine. My question is why would an insert trigger cause this anomaly?
> Thanks, Steven
> CREATE TRIGGER tgrBusinessEmployee_i ON dbo.tblBusinessEmployee
> FOR INSERT
> AS
> INSERT into tlogBusinessEmployee
> (EmployeeID, BusinessLocation, LastName, FirstName, NickName,
> WebPassword, Title, --CurriculumVitae,
> ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
> yn_ActiveEmployee, yn_PublishToWeb,
> yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
> LogProcess)
> SELECT
> Employee_ID, BusinessLocation, LastName, FirstName, NickName,
> WebPassword, Title, --Convert(varchar(5000),CurriculumVitae),
> ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
> yn_ActiveEmployee, yn_PublishToWeb,
> yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
> 'InsertUpdate'
> FROM inserted
>
|||Scott,
The answer is yes to both questions, but I am not using the Employee_ID as
the PK and identity column in the log table:
CREATE TABLE [tblBusinessEmployee] (
[Employee_ID] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [pk_BusinessEmployee] PRIMARY KEY CLUSTERED
(
[Employee_ID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [tlogBusinessEmployee] (
[EmployeeLog_ID] [int] IDENTITY (1, 1) NOT NULL ,
[EmployeeID] [int] NULL ,
CONSTRAINT [pk_LogBusinessEmployee] PRIMARY KEY CLUSTERED
(
[EmployeeLog_ID]
) ON [PRIMARY]
) ON [PRIMARY]
INSERT into tlogBusinessEmployee
(EmployeeID, BusinessLocation, LastName, FirstName, NickName,
WebPassword, Title, --CurriculumVitae,
ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
yn_ActiveEmployee, yn_PublishToWeb,
yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
LogProcess)
SELECT
Employee_ID, BusinessLocation, LastName, FirstName, NickName,
WebPassword, Title, --Convert(varchar(5000),CurriculumVitae),
ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
yn_ActiveEmployee, yn_PublishToWeb,
yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
'InsertUpdate'
FROM inserted
"Scott Morris" <bogus@.bogus.com> wrote in message
news:e9BhBwLnEHA.1236@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Sounds like a problem with the client code. Are you using an identity
> column as the PK for this table? Does the log table also have an identity
> column?
> "Steven K0" <stroy@.api.com> wrote in message
> news:efT2ywFnEHA.3392@.TK2MSFTNGP15.phx.gbl...
> new
> show
works[vbcol=seagreen]
|||As I indicated, the problem is in the client application. Most likely, the
application is using @.@.identity to identify the ID of the inserted employee
row, which would be incorrect in this case (this is supported by the remark
that the application works correctly when the insert trigger logic is
removed). use scope_identity() if using sql2k. Use of the profiler may help
to identify issues with the conversation between the client and the server.
"Steven K" <skaper@.troop.com> wrote in message
news:e352JZOnEHA.952@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Scott,
> The answer is yes to both questions, but I am not using the Employee_ID as
> the PK and identity column in the log table:
> CREATE TABLE [tblBusinessEmployee] (
> [Employee_ID] [int] IDENTITY (1, 1) NOT NULL ,
> CONSTRAINT [pk_BusinessEmployee] PRIMARY KEY CLUSTERED
> (
> [Employee_ID]
> ) ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
> CREATE TABLE [tlogBusinessEmployee] (
> [EmployeeLog_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [EmployeeID] [int] NULL ,
> CONSTRAINT [pk_LogBusinessEmployee] PRIMARY KEY CLUSTERED
> (
> [EmployeeLog_ID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>
>
> INSERT into tlogBusinessEmployee
> (EmployeeID, BusinessLocation, LastName, FirstName, NickName,
> WebPassword, Title, --CurriculumVitae,
> ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
> yn_ActiveEmployee, yn_PublishToWeb,
> yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
> LogProcess)
> SELECT
> Employee_ID, BusinessLocation, LastName, FirstName, NickName,
> WebPassword, Title, --Convert(varchar(5000),CurriculumVitae),
> ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
> yn_ActiveEmployee, yn_PublishToWeb,
> yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
> 'InsertUpdate'
> FROM inserted
>
> "Scott Morris" <bogus@.bogus.com> wrote in message
> news:e9BhBwLnEHA.1236@.TK2MSFTNGP09.phx.gbl...
identity[vbcol=seagreen]
a[vbcol=seagreen]
will
> works
>

Insert Trigger causes new record to disappear

Hello, I am using the following trigger. After I insert a new record a new
record into the table, the record disappears and a previous record will show
up as the last record. If I change it to just an update trigger, it works
fine. My question is why would an insert trigger cause this anomaly?
Thanks, Steven
CREATE TRIGGER tgrBusinessEmployee_i ON dbo.tblBusinessEmployee
FOR INSERT
AS
INSERT into tlogBusinessEmployee
(EmployeeID, BusinessLocation, LastName, FirstName, NickName,
WebPassword, Title, --CurriculumVitae,
ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
yn_ActiveEmployee, yn_PublishToWeb,
yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
LogProcess)
SELECT
Employee_ID, BusinessLocation, LastName, FirstName, NickName,
WebPassword, Title, --Convert(varchar(5000),CurriculumVitae),
ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
yn_ActiveEmployee, yn_PublishToWeb,
yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
'InsertUpdate'
FROM insertedSteven,
Perhaps the insert into tlogBusinessEmployee inside the trigger is failing,
causing the insert into tblBusinessEmployee to fail.
If you add error handling to the trigger, you can detect an insert error and
debug (through a print statement) what the trigger is actually doing.
Alternatively, you can step through an insert in Query Analyzer or Visual
Studio, and debug the trigger.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Steven K0" <stroy@.api.com> wrote in message
news:efT2ywFnEHA.3392@.TK2MSFTNGP15.phx.gbl...
> Hello, I am using the following trigger. After I insert a new record a
new
> record into the table, the record disappears and a previous record will
show
> up as the last record. If I change it to just an update trigger, it works
> fine. My question is why would an insert trigger cause this anomaly?
> Thanks, Steven
> CREATE TRIGGER tgrBusinessEmployee_i ON dbo.tblBusinessEmployee
> FOR INSERT
> AS
> INSERT into tlogBusinessEmployee
> (EmployeeID, BusinessLocation, LastName, FirstName, NickName,
> WebPassword, Title, --CurriculumVitae,
> ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
> yn_ActiveEmployee, yn_PublishToWeb,
> yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
> LogProcess)
> SELECT
> Employee_ID, BusinessLocation, LastName, FirstName, NickName,
> WebPassword, Title, --Convert(varchar(5000),CurriculumVitae),
> ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
> yn_ActiveEmployee, yn_PublishToWeb,
> yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
> 'InsertUpdate'
> FROM inserted
>|||Sounds like a problem with the client code. Are you using an identity
column as the PK for this table? Does the log table also have an identity
column?
"Steven K0" <stroy@.api.com> wrote in message
news:efT2ywFnEHA.3392@.TK2MSFTNGP15.phx.gbl...
> Hello, I am using the following trigger. After I insert a new record a
new
> record into the table, the record disappears and a previous record will
show
> up as the last record. If I change it to just an update trigger, it works
> fine. My question is why would an insert trigger cause this anomaly?
> Thanks, Steven
> CREATE TRIGGER tgrBusinessEmployee_i ON dbo.tblBusinessEmployee
> FOR INSERT
> AS
> INSERT into tlogBusinessEmployee
> (EmployeeID, BusinessLocation, LastName, FirstName, NickName,
> WebPassword, Title, --CurriculumVitae,
> ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
> yn_ActiveEmployee, yn_PublishToWeb,
> yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
> LogProcess)
> SELECT
> Employee_ID, BusinessLocation, LastName, FirstName, NickName,
> WebPassword, Title, --Convert(varchar(5000),CurriculumVitae),
> ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
> yn_ActiveEmployee, yn_PublishToWeb,
> yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
> 'InsertUpdate'
> FROM inserted
>|||Scott,
The answer is yes to both questions, but I am not using the Employee_ID as
the PK and identity column in the log table:
CREATE TABLE [tblBusinessEmployee] (
[Employee_ID] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [pk_BusinessEmployee] PRIMARY KEY CLUSTERED
(
[Employee_ID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [tlogBusinessEmployee] (
[EmployeeLog_ID] [int] IDENTITY (1, 1) NOT NULL ,
[EmployeeID] [int] NULL ,
CONSTRAINT [pk_LogBusinessEmployee] PRIMARY KEY CLUSTERED
(
[EmployeeLog_ID]
) ON [PRIMARY]
) ON [PRIMARY]
INSERT into tlogBusinessEmployee
(EmployeeID, BusinessLocation, LastName, FirstName, NickName,
WebPassword, Title, --CurriculumVitae,
ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
yn_ActiveEmployee, yn_PublishToWeb,
yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
LogProcess)
SELECT
Employee_ID, BusinessLocation, LastName, FirstName, NickName,
WebPassword, Title, --Convert(varchar(5000),CurriculumVitae),
ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
yn_ActiveEmployee, yn_PublishToWeb,
yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
'InsertUpdate'
FROM inserted
"Scott Morris" <bogus@.bogus.com> wrote in message
news:e9BhBwLnEHA.1236@.TK2MSFTNGP09.phx.gbl...
> Sounds like a problem with the client code. Are you using an identity
> column as the PK for this table? Does the log table also have an identity
> column?
> "Steven K0" <stroy@.api.com> wrote in message
> news:efT2ywFnEHA.3392@.TK2MSFTNGP15.phx.gbl...
> > Hello, I am using the following trigger. After I insert a new record a
> new
> > record into the table, the record disappears and a previous record will
> show
> > up as the last record. If I change it to just an update trigger, it
works
> > fine. My question is why would an insert trigger cause this anomaly?|||As I indicated, the problem is in the client application. Most likely, the
application is using @.@.identity to identify the ID of the inserted employee
row, which would be incorrect in this case (this is supported by the remark
that the application works correctly when the insert trigger logic is
removed). use scope_identity() if using sql2k. Use of the profiler may help
to identify issues with the conversation between the client and the server.
"Steven K" <skaper@.troop.com> wrote in message
news:e352JZOnEHA.952@.TK2MSFTNGP10.phx.gbl...
> Scott,
> The answer is yes to both questions, but I am not using the Employee_ID as
> the PK and identity column in the log table:
> CREATE TABLE [tblBusinessEmployee] (
> [Employee_ID] [int] IDENTITY (1, 1) NOT NULL ,
> CONSTRAINT [pk_BusinessEmployee] PRIMARY KEY CLUSTERED
> (
> [Employee_ID]
> ) ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
> CREATE TABLE [tlogBusinessEmployee] (
> [EmployeeLog_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [EmployeeID] [int] NULL ,
> CONSTRAINT [pk_LogBusinessEmployee] PRIMARY KEY CLUSTERED
> (
> [EmployeeLog_ID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>
>
> INSERT into tlogBusinessEmployee
> (EmployeeID, BusinessLocation, LastName, FirstName, NickName,
> WebPassword, Title, --CurriculumVitae,
> ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
> yn_ActiveEmployee, yn_PublishToWeb,
> yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
> LogProcess)
> SELECT
> Employee_ID, BusinessLocation, LastName, FirstName, NickName,
> WebPassword, Title, --Convert(varchar(5000),CurriculumVitae),
> ContactType, ContactOrder, Email, Phone, TollFree, Fax, WWW,
> yn_ActiveEmployee, yn_PublishToWeb,
> yn_LockOut, yn_Remove, DateEntered, SortKey, ContactID, FirmID,
> 'InsertUpdate'
> FROM inserted
>
> "Scott Morris" <bogus@.bogus.com> wrote in message
> news:e9BhBwLnEHA.1236@.TK2MSFTNGP09.phx.gbl...
> > Sounds like a problem with the client code. Are you using an identity
> > column as the PK for this table? Does the log table also have an
identity
> > column?
> >
> > "Steven K0" <stroy@.api.com> wrote in message
> > news:efT2ywFnEHA.3392@.TK2MSFTNGP15.phx.gbl...
> > > Hello, I am using the following trigger. After I insert a new record
a
> > new
> > > record into the table, the record disappears and a previous record
will
> > show
> > > up as the last record. If I change it to just an update trigger, it
> works
> > > fine. My question is why would an insert trigger cause this anomaly?
>

Insert Trigger and Updating a view

I am just getting back to SqlServer and TSQL after a 4 year hiatus.
I want to write a trigger to update a view with the same record that is
being inserted into a table. I have a trigger bound to the table to be
inserted and since it is a simple process, I will probably forgoing using a
stored proc.
In my trigger I want to essentially do:
On Insert....
Update MyView
Set Col A = NewCol A Value,
Col B = NewCol B Value,
Col C = NewCol C Value
The NewCol x Value values are the insert values of the record being posted
to the table being inserted.
Interbase has New property. Can anyone provide the syntac to accomplish my
task?
TIA
LarryLarry,
There are two special tables accessible within a trigger,
inserted and deleted. They hold the new rows (for inserts
and updates) and the old rows (for deletes and updates)
of the target table with respect to the statement that fired
the trigger. Note that a trigger fires only once, whether the
triggering statement affects multiple rows or not, and so the
inserted and deleted tables can have more than one row.
It sounds like your triggering statement will be affecting
only one row, but it is still a good idea to consider making
sure of that by checking @.@.rowcount at the very beginning
of the trigger.
Your trigger will probably look something like this:
create trigger... as
if @.@.rowcount <> 1 begin
raiserror (as appropriate)
rollback transaction -- or return, or whatever you need
update MyView set
ColA = i.ColA,
ColB = i.ColB,
. and so on
where MyView.viewKey = i.ColumnIdentifyingViewRowToUpdate
If you want, post CREATE TABLE statement and sample data for an
example and we can try to help more specifically to your case. You
can also find out more about the special tables inserted and deleted
in Books Online.
Steve Kass
Drew University
DelphiGuy wrote:

>I am just getting back to SqlServer and TSQL after a 4 year hiatus.
>I want to write a trigger to update a view with the same record that is
>being inserted into a table. I have a trigger bound to the table to be
>inserted and since it is a simple process, I will probably forgoing using a
>stored proc.
>In my trigger I want to essentially do:
>On Insert....
>Update MyView
>Set Col A = NewCol A Value,
> Col B = NewCol B Value,
> Col C = NewCol C Value
>
>The NewCol x Value values are the insert values of the record being posted
>to the table being inserted.
>Interbase has New property. Can anyone provide the syntac to accomplish my
>task?
>TIA
>Larry
>
>

Insert Trigger

I have a table that has a unique ID field. When a new record is inserted into the table I would like to insert the ID into 3 other tables. I am new to triggers and am not sure how to handle this. Any idea how the trigger would be written?

I don't have a server around at the moment to provide you with working code, but all in all you have to create an AFTER INSERT trigger that will insert value inserted.ID (inserted is a system name of result set that holds everything that user inserted into the main table) to other 3 tables.

Feel free to ask if you still have trouble with this.

|||

Hi,

keep in mind that the insert trigger will be always fired once per statement not per row. So getting you Id won′t work if you put it in a variable or something like this, a pseudotrigger for you could be something like

CREATE TRIGGER SomeTrigger
ON SOMETable
FOR INSERT
AS
BEGIN

INSERT INTO FirstTable(Columnlist,Othercolumns)
SELECT ID, Othercolumns FROM Inserted

INSERT INTO SecondTable(Columnlist,Othercolumns)
SELECT ID, Othercolumns FROM Inserted

INSERT INTO ThirdTable(Columnlist,Othercolumns)
SELECT ID, Othercolumns FROM Inserted

END

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Insert Trigger

I have a table that has a unique ID field. When a new record is inserted into the table I would like to insert the ID into 3 other tables. I am new to triggers and am not sure how to handle this. Any idea how the trigger would be written?

I don't have a server around at the moment to provide you with working code, but all in all you have to create an AFTER INSERT trigger that will insert value inserted.ID (inserted is a system name of result set that holds everything that user inserted into the main table) to other 3 tables.

Feel free to ask if you still have trouble with this.

|||

Hi,

keep in mind that the insert trigger will be always fired once per statement not per row. So getting you Id won′t work if you put it in a variable or something like this, a pseudotrigger for you could be something like

CREATE TRIGGER SomeTrigger
ON SOMETable
FOR INSERT
AS
BEGIN

INSERT INTO FirstTable(Columnlist,Othercolumns)
SELECT ID, Othercolumns FROM Inserted

INSERT INTO SecondTable(Columnlist,Othercolumns)
SELECT ID, Othercolumns FROM Inserted

INSERT INTO ThirdTable(Columnlist,Othercolumns)
SELECT ID, Othercolumns FROM Inserted

END

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

sql

Insert Trigger

I am facing problem in creating a insert trigger for the following scenario.

i have transactions, control tables

whenever i insert a record in transactions it should get value from the control table, increment that value in control table and update the same value as transaction_id for new transaction in transaction table.

control table has these fields (control_desc, control_value)

can some one help me to write a trigger (insert trigger) in transactions table.

Tanks for the Help

Coudl you please provide more information like DDL code, showing which column you want to update etc.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks for the replay

Fields in Transactions Table : trans_id,trans_type,amount,trans_date,uid

Fields in Control Table: id, id_value

I am inserting all the fields in transction table except trans_id when insert trigger fires i want to take id_value from control table which id is "trans_id" in id coulmn update the same in the trans_id field in transactiontable.

Can u help me reating this trigger.

Regards

|||

Try this

--<Run Once>

drop table Transactions

drop table control

go

create table control

(

control_desc varchar(5) not null primary key,

control_value int not null

)

create table Transactions

(

trans_id int not null identity,

control_desc varchar(5) not null foreign key references control(control_desc),

control_value int not null

)

go

create trigger ti_Transactions on Transactions for insert

as

set nocount on

update c

set c.control_value = c.control_value + 1

from control c

join inserted i

on i.control_desc = c.control_desc

go

insert control select 'ABCDE', 10001

go

--</Run Once>

--<Repeatable>

insert transactions (control_desc, control_value)

select control_desc, control_value

from control

where control_desc = 'ABCDE'

go

select * from control

select * from transactions

--</Repeatable>

|||

The following query may help you...

Code Snippet

create table control (
id int,
id_value int)

Go

create table Transactions (
trans_id int,
trans_type int,
amount float,
trans_date datetime,
uid uniqueidentifier)

Go

Insert Into control values(1,0)--Initiating the value

Go

Create Trigger Trg_Insert_Transactions
On Transactions For Insert
As
Begin
SET NOCOUNT ON;
Declare @.Id as int;

Update control WITH (ROWLOCK)
Set
@.Id = Id_value = (Id_Value +1)
Where
id =1;
Update Transactions
Set
trans_id = @.Id
Where
uid = (Select Uid From Inserted)
End

Go

Insert Into Transactions values(null, 1, 10,getdate(),newid())

GO

select * from Transactions
select * from control

Insert Trigger

I am facing problem in creating a insert trigger for the following scenario.

i have transactions, control tables

whenever i insert a record in transactions it should get value from the control table, increment that value in control table and update the same value as transaction_id for new transaction in transaction table.

control table has these fields (control_desc, control_value)

can some one help me to write a trigger (insert trigger) in transactions table.

Tanks for the Help

Coudl you please provide more information like DDL code, showing which column you want to update etc.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks for the replay

Fields in Transactions Table : trans_id,trans_type,amount,trans_date,uid

Fields in Control Table: id, id_value

I am inserting all the fields in transction table except trans_id when insert trigger fires i want to take id_value from control table which id is "trans_id" in id coulmn update the same in the trans_id field in transactiontable.

Can u help me reating this trigger.

Regards

|||

Try this

--<Run Once>

drop table Transactions

drop table control

go

create table control

(

control_desc varchar(5) not null primary key,

control_value int not null

)

create table Transactions

(

trans_id int not null identity,

control_desc varchar(5) not null foreign key references control(control_desc),

control_value int not null

)

go

create trigger ti_Transactions on Transactions for insert

as

set nocount on

update c

set c.control_value = c.control_value + 1

from control c

join inserted i

on i.control_desc = c.control_desc

go

insert control select 'ABCDE', 10001

go

--</Run Once>

--<Repeatable>

insert transactions (control_desc, control_value)

select control_desc, control_value

from control

where control_desc = 'ABCDE'

go

select * from control

select * from transactions

--</Repeatable>

|||

The following query may help you...

Code Snippet

create table control (
id int,
id_value int)

Go

create table Transactions (
trans_id int,
trans_type int,
amount float,
trans_date datetime,
uid uniqueidentifier)

Go

Insert Into control values(1,0)--Initiating the value

Go

Create Trigger Trg_Insert_Transactions
On Transactions For Insert
As
Begin
SET NOCOUNT ON;
Declare @.Id as int;

Update control WITH (ROWLOCK)
Set
@.Id = Id_value = (Id_Value +1)
Where
id =1;
Update Transactions
Set
trans_id = @.Id
Where
uid = (Select Uid From Inserted)
End

Go

Insert Into Transactions values(null, 1, 10,getdate(),newid())

GO

select * from Transactions
select * from control

sql