insert/update/delete without replication

Hi,
i have a peer to peer replication set up between 2 databases. There
was a parallel insert the databases are out of sync. i know the table
where the difference is. Is there any stored procedure with which i
can insert/update/delete without it being replicated?
You could use the SKIPERRORS flag, or you could modify the relevant
rteplication stored procedure on the subscriber to skip the change.
HTH,
Paul Ibison

Insert/update/delete Transaction

Hi,

I have an unbound DataGridView and I have load it with a set of records from a Data base.

I modify existing rows, delete rows and add new rows to DataGridView control. I have to send a new modified dataset back to the data base.

Please any suggestions how to solve the problem?

Thanks in advance

George

Hi George,

I think you'll have more success posting your question on the Visual Studio forums - this is the T-SQL forum which is primarily used for back-end SQL questions, rather than user interface coding problems like DataGridViews.

Hope that helps :)

Menthos
|||Thanks :)sql

Insert/Update and Delete Change

hi,

I have a table which contained 5 columns and with 2 primary keys

Col 1 | Col 2 | Col 3 | Col 4 | Col 5 |

ab | 1 | abc | null | null

ab | 2 | def | null | null

Col 1 and Col 2 both are primary keys.

How do I update Col2 from 1 to 2 and from 2 to 1 in a single transaction statement and commit it?

Thanks

SQL is a set-based language so you can do below in an UPDATE statement to swap the column values:

update tbl

set Col2 = case Col2 when 1 then 2 when 2 then 1 end

where Col2 in (1, 2)

Logically with DML statements all the rows are affected at once so the constraint violation will not happen in your particular case. The technique that allows such changes to happen is called Halloween Protection. This is done by using table spools or split operators. If you look at the showplan of the above update statement, you can see it in action.

|||

update table1
set col2=case col2 when 1 then 2 else 1 end

You should pay attention to the order by the data.

/*

col1 col2 col3 col4 col5
- -- - - -
ab 1 def NULL NULL
ab 2 abc NULL NULL

*/

First, I was cheated by the order.

Insert/Delete Trigger Misfires

I am having problems with a trigger that is designed to audit changes to a particular field in a table. If that field is updated, then the old record is inserted into an audit table.

This trigger never fails when I run test data against it from Query Analyzer. It works some of the time when the web application updates it, fails other times.

Typically, multiple records are updated at the same time. Any ideas?

Here is the Trigger:

create trigger t_u_product_rate_detail
on product_rate_detail
for insert, update, delete

as

/--Local variable
declare
@.auditdate datetime,
@.audituser sysname

--Set values so function isn't executed a bunch of times
select
@.auditdate = getdate(),
@.audituser = suser_sname()

if exists (select * from inserted)
begin
if exists (select * from deleted)
begin
insert into product_rate_detail_audit_log
select d.product_rate_detail_id,
d.product_rate_id,
d.day_of_week_id,
d.ad_size_id,
d.rate,
d.plan_vol,
d.plan_freq,
@.auditdate, @.audituser, 'U'
from deleted d
join inserted i on i.product_rate_detail_id = d.product_Rate_detail_id
where (d.rate <> 0 and d.rate is not null)
and i.rate <> d.rate -- this determines if the rate has changed.
end
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOLots of stuff wrong with this. Heres some things to consider:
Your trigger is for insert/update/delete, but this criteria:if exists (select * from inserted)
begin
if exists (select * from deleted)
begin...will cause your insert statement to execute only on updates, because that is the only occasion when data exists in both inserted and deleted tables.
You really don't need to check for the existence of data in the inserted and deleted tables anyway. If you reference them as a source of data in a statement and they are empty, then your statement will just not do anything. So drop the "if exists" clauses completely.
If you just want to capture updates and deletes then you need only reference the deleted table. The inserted table contains the new values, and looks like you are not archiving those (until they themselves are updated).
You can also drop the @.AuditDate and @.AuditUser variables, and just reference getdate() and suser_sname() directly in your update statement. suser_sname() is constant throughout the transaction, and unless you have a truly massive update then getdate() will return a consistent value across all affected records as well.
Dropping your exists clauses and your unnecessary variable declarations will simplify your code, and simpler is always better.|||Well, let me bite ...

First, check for existence is always a good idea, simply because attempting to perform an operation on an empty set also has its cost and contributes to resource contention. Besides the trigger will get fired even if 0 rows are updated.

Second, if you all want to use best practices, - do not perform mass updates, so that the trigger does not have to kill the server while processing millions of rows. Also, (and this is truly the best practice point) - read your virtual tables only once, because this opration in itself is extremely expensive. In order to satisfy this requirement, - select * into #tmp from deleted!

Third, - remove references to INSERT and DELETE in the trigger definition. UPDATE occurs only when a record is updated, not when it is deleted or inserted (unless your app performs UPDATE by issueing DELETE+INSERT).

Insert/ Update/ Delete slowness.

SQL2K sp4

Howdy all. I opened a 200 mb. file in Query Analyzer that is full of Inserts/ Updates/ and Deletes. I tried just to parse it, and killed it after 18 hours. There is no blocking. All of the appropriate indexes exist. I even removed them and retried JIC. The box is plenty powerful for this task. Does anyone have any ideas?
I've tried several times with no luck. At the top of the file is SET IMPLICIT_TRANSACTIONS ON and then every 10,000 statements is COMMIT WORK. I've tried adjusting the number of commits to a lower number with no luck. This works fine on smaller files (3 - 20 mb).Can you give a sample of the statements this script is running?

Insert, Update & Delete on two tables with same data structure...

I have created two table with same data structure. I need realtime effects (i.e. data) on both tables - Table1 & Table2.

Following Points to Consider.

1. Both tables are in the same database.

2. Table1 is using for data entry & I wants the same data in the Table2.

3. If any row insert, update & delete occers on Table1, the same effect should be done on Table2.

4. I need real time data insert, update & delete on Table2.

I knew that using triggers it could be possible, I have successfully created a trigger for inserting new rows (using logical table "Inserted") in Table2 but not succeed for update & delete yet.

I want to understand how can I impletement this successfully without any ambiguity.

I have attached data structure for tables. Thanx...You want TWO tables with IDENTICAL structure and the SAME data in a SINGLE database?
We can help you debug your triggers if you post the code, but WHY?|||Actually we required some reports and as we have old version application, it could not be possible to generate required reports.

The data is dynamic (i.e. Table1) & changing with the stock quantity IN & OUT, thats why I will store data for specific span of time in the new table (Table2). I will use that data for reporting.

Which code you required..? I have attached script for creating a tables.|||I understand you want inserts copied to the second table.
What about updates? Do you want the data in the second table updated, or do you want a new record added instead?
What about deletes? Do you want the data in the second table deleted as well, or do you just want to mark the record as deleted?

Did you try writing triggers for Update and Delete? If so, post the code for those triggers and we will help you debug it our fix syntax errors.|||oye...redundant data...

In any case if you follow the Hint link sticky at the top of the forum and post what it tells you, I'm sure we can supply enough rope|||As I have told that the data entry done through frontend, I want each and every effect (i.e. row insert, update or delete) on Table2.

As I have told you that the second table I am using for reporting purpose & the reports will be wrong if it is not reflect the data which entered or modified last.

May be you think its too cumbersome but now let me explain the full scenario.

1. I have to do this because I have added new column in the Table2 which is not part of Table1.

2. Using insert trigger on Table1 I can add new row in Table2 same as Table1 as well as I can feed data in the new added column which is not part of Table1.

3. Whenever row inserted, update or delete in Table1 the Table2 should update accordingly.

4. I can not cascade update or delete because both tables are having only foreign keys. (cFinYrs, cLocCode, cMonth, cItemCode are foreign keys)

5. I will re-write triggers according to my requirement, but I need little help to be clear of the concept from you expert guys.

6. The script which I have given for creating a tables will create the same data structure for tables.

I have written trigger for insert, it's given below. It's working good for insert.

CREATE TRIGGER [InForRpt] ON [dbo].[Table1]
FOR INSERT

AS

Declare @.cFinYrs varchar(3)
Declare @.cLocCode varchar(7)
Declare @.cMonth varchar(3)
Declare @.iSrNo int
Declare @.cItemCode varchar(20)
Declare @.dQty dec
Declare @.dRate dec(9,2)
Declare @.cDesc varchar(200)
Declare @.cCreated varchar(6)
Declare @.dtcreated datetime
Declare @.cModified varchar(6)
Declare @.dtModified datetime
Declare @.cMachIP varchar(15)

SET @.cFinYrs = (select cFinYrs from inserted)
SET @.cLocCode = (select cLocCode from inserted)
SET @.cMonth = (select cMonth from inserted)
SET @.iSrNo = (select iSrNo from inserted)
SET @.cItemCode = (select cItemCode from inserted)
SET @.dQty = (select dQty from inserted)
SET @.cDesc = (select cDesc from inserted)
SET @.cCreated = (select cCreated from inserted)
SET @.dtcreated = (select dtCreated from inserted)
SET @.cModified = (select cModified from inserted)
SET @.dtModified = (select dtModified from inserted)
SET @.cMachIP = (select cMachIP from inserted)

Select @.dRate = drate from ssstockmst where citemcode=@.cItemCode

Insert INTO Table2 values(@.cFinYrs, @.cLocCode, @.cMonth,
@.iSrNo, @.cItemCode, @.dQty,
@.dRate, @.cDesc, @.cCreated,
@.dtCreated, @.cModified,
@.dtModified, @.cMachIP)

How I can make this happen..? Thanx for replying...|||oye...redundant data...

Yeah it could be redundant data but it will helps me lot to produce reports according to management requirement. And this data will not be heavy in size (1 to 5MB) so don't affect the server space as we have provision for same. :)|||I have to do this because I have added new column in the Table2 which is not part of Table1.This still makes no sense. Why not just add the column to Table1? Are you dealing with a reduced record set in table2? Is that data truncated occasionally, or filtered? We need to know how that data is being retained before helping you create Update/Delete triggers.

But regarding your insert trigger...

The method you have chosen is not only slow and verbose, but will also fail if more than one record is inserted into the table by a single transaction. Triggers MUST be designed to function correctly with multi-record inserts.

No exceptions.

This is the method you want to use for your insert trigger:CREATE TRIGGER [InForRpt] ON [dbo].[Table1]
FOR INSERT

AS
begin
insert into Table2
(cFinYrs,
cLocCode,
cMonth,
iSrNo,
cItemCode,
dQty,
dRate,
cDesc,
cCreated,
dtCreated,
cModified,
dtModified,
cMachIP)
select inserted.cFinYrs,
inserted.cLocCode,
inserted.cMonth,
inserted.iSrNo,
inserted.cItemCode,
inserted.dQty,
ssstockmst.dRate,
inserted.cDesc,
inserted.cCreated,
inserted.dtCreated,
inserted.cModified,
inserted.dtModified,
inserted.cMachIP
from inserted
left outer join ssstockmst on inserted.cItemCode = ssstockmst.cItemCode
endMuch simpler, eh?
Now, I really recommend that you go back to Books Online and read the sections on triggers, paying careful attention to the examples given.|||This still makes no sense. Why not just add the column to Table1? Are you dealing with a reduced record set in table2? Is that data truncated occasionally, or filtered? We need to know how that data is being retained before helping you create Update/Delete triggers.

I thought to add new column in the Table1 but Table1 is being used & lots of data in the table1. Second thing, I have to think of the forntend application too.

We found best solution for a while is to create a new table for same & we will update the table1 later, when we upgrade our database & application.

No, data is truncated...

Thanx blindman, for simplify insert trigger...|||I thought to add new column in the Table1 but Table1 is being used & lots of data in the table1. Second thing, I have to think of the forntend application too.Still makes no sense. You're taking up extra space by storing redundant data in table2, extra processing time by keeping the data synchronized, extra development time in setting up this process, and a properly designed front-end won't care or even know that you've added an extra column to the table.
You need a DBA to help you with this project...|||I have solved the problem.

1. I have created a surrogate key (combination of cFinYrs, cLocCode, cMonth & cItemCode) on Table1 and accordingly foreign key on Table2.

2. Set cascade for update & delete.

I have test it, it's working fine.

I am understanding what you want to say but right now I am not allowed to modify working table's structure. Surely I will do it but later.

Thanx blindman for all efforts you placed...

insert, select, update and delete

I've got four pages with in the first page a insert, in the second a select, in the thirth a update and in the fourth a delete statement. First the values of a textbox will be inserted in the database, then the values will be shown in labels and than it is possible to edit or delete the values inserted. Every inserted item belonging to each other has one ID. The follwing values has a second ID etc.

How can I make that possible?? I think that I should pass the ID's between the pages so I'm sure that I edit or delete the values that I want. So insert value 1 in page 1, show with select value 1 in page 2, edit or delete value 1 in page 3 and 4.

Maybe I didn't explain it good enough for you, please tell me then!!

Thanks!!

I think I got a solution for it. On every top of the page the user can select a value in a dropdownlist. The selected value calls the database and selects the appropriate row. Now I can update and delete the row I want. To update the values of the database I got for each value a textboxt. On selectedindexchanged the textbox.text will filled with the values of the appropriate row and now I can adjust the textboxes and update the values. Or I can delete the row with another button. This is in theory but practical it's hard. Does someone has suggestions or hints??|||Which version of ASP.NET and which version of SQL Server are you using? Your questions are a bit too vague (and possibly in the incorrect forum) to be able to be of much help to you.

If you are using ASP.NET 2.0 you might try theTutorials on this site.|||I was affraid of that, second try:
I have a dropdownlist with values as NEW and BonsaiName1, BonsaiName2. When the NEW is selected all textboxes are empty and can be filled with data that can be inserted into the database and BonsaiName3 is created. When BonsaiName1 or BonsaiName2 etc is selected the textboxes should be filled with the correspondending data out of the database with a SELECT procedure. They can now be edited by a UPDATE or DELETE statement.
My question is: When I got a dropdownlist1.selectedvalue how do I get this in the SELECTstatement in the WHEREpart. I think the best way is with a parameter but how?
I'm running ASP.NET 2.0 and SQL Server 2005.
Thanks!!

insert, delete, update stored procedures in Snapshot

Just a general question ....
When the initial snapshot for a replication runs, I see it creates a
bunch of sp_MSins, sp_MSdel, and sp_MSupd, 3 stored procedures for
each of the articles in my publication.
I see these sp's at the subscriber db, but what I do not understand is
why I see 2 stored procedures that are identical, one named with
bracquets, the other without bracquets, e.g. [sp_MSupd_Entity Address]
and sp_MSupd_Entity Address ?
My current replication fails because it tries to run sp_MSupd_Entity
Address that apparently never gets created. But [sp_MSupd_Entity
Address] is created.
how can I tell the replication to use [sp_MSupd_Entity Address] not
sp_MSupd_Entity Address ??
Having SQL Server create two sets of replication stored procedures is an error.
SQL Server will by default autogenerate stored procedures in the unbracketed form, ie sp_MSupd_Entity Address.
It looks like the space is in the table name is causing this problem, but I am unable to reproduce your problem on SQL 2000 sp3 8.000.818. What version are you running.
Do a sp_browsereplcmds in your distribution database to see what procs SQL Server is using.
To answer your question, if you need to change the proc name that SQL Server is using right click on your publication, click on the articles tab, click on the browse button to the right of your table name, click on commands tab, and make the changes there

insert, delete, update data in database

hi. i'm trying to create a c# application which would insert, update and delete data from a database. could anyone pls point me to the right direction in which i should take? thanks in advance.

You are talking about a Compact Framework application that leverages SQL Mobile for on-device data persistence. To get started with these technologies, start here: http://msdn.microsoft.com/mobility/gettingstarted/default.aspx

You can also download the IBuySpyStore sample application from GotDotNet, which is a complete example of a CF2 application working with SQL Mobile.

Darren

insert to view vs. insert directly to table

Will I see performance degrading if I use commands to insert / delete and
select from a view as supposed to doing it directly from a table
The view is a simple select * from TableName
INdirected one level as you have, no.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Avi" <rememberoti@.yahoo.com> wrote in message
news:uHkW5FOlEHA.3520@.tk2msftngp13.phx.gbl...
> Will I see performance degrading if I use commands to insert / delete and
> select from a view as supposed to doing it directly from a table
>
> The view is a simple select * from TableName
>
>
|||Avi,
Firstly, your view should not be select * from table, it should be
select <columnlist> from table.
You can only insert into a view if the view only references a base
table. Why have you got a view that does a select * anyway? Why not just
insert into the table?
You could try benchmarking this yourself to see if there's any
difference, should be pretty easy to set up. I don't think you'll notice
much of a difference, though the direct insert into the table probably
will win it by nanoseconds.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Avi wrote:
> Will I see performance degrading if I use commands to insert / delete and
> select from a view as supposed to doing it directly from a table
>
> The view is a simple select * from TableName
>
>
sql

insert to view vs. insert directly to table

Will I see performance degrading if I use commands to insert / delete and
select from a view as supposed to doing it directly from a table
The view is a simple select * from TableNameINdirected one level as you have, no.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Avi" <rememberoti@.yahoo.com> wrote in message
news:uHkW5FOlEHA.3520@.tk2msftngp13.phx.gbl...
> Will I see performance degrading if I use commands to insert / delete and
> select from a view as supposed to doing it directly from a table
>
> The view is a simple select * from TableName
>
>|||Avi,
Firstly, your view should not be select * from table, it should be
select <columnlist> from table.
You can only insert into a view if the view only references a base
table. Why have you got a view that does a select * anyway? Why not just
insert into the table?
You could try benchmarking this yourself to see if there's any
difference, should be pretty easy to set up. I don't think you'll notice
much of a difference, though the direct insert into the table probably
will win it by nanoseconds.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Avi wrote:
> Will I see performance degrading if I use commands to insert / delete and
> select from a view as supposed to doing it directly from a table
>
> The view is a simple select * from TableName
>
>

Insert query with nested select and parameter

hey there, i'm trying to move one record from one table to the next,
so i'm doing this by doing an insert into table, then delete from the
previous table. Only thin g is on the insert i want to include a
parameter. Can't work how to do this. Do you think i need to do
another update query and insert the parameter that way, after the
insert is done?

ALTER PROCEDURE dbo.ReturnLoan

(
@.strBarcode varchar(100),
@.strLibrary varchar(100),
@.dateReturned datetime
)

AS
INSERT INTO Loan_History
(
Barcode,
UserID,
Date_Borrowed,
Library

)
Select Barcode, UserID, Date_Borrowed, Library FROM Loans WHERE
Barcode = @.strBarcode
AND Library = @.strLibrary
;

DELETE FROM Loans
WHERE Barcode = @.strBarcode
AND Library = @.strLibrary ;

RETURNOn 19 Oct 2004 00:04:39 -0700, Mark wrote:

(snip)
> on the insert i want to include a
>parameter.

INSERT INTO Loan_History
(
Barcode,
UserID,
Date_Borrowed,
Library,
SomeOtherColumn

)
SELECTBarcode,
UserID,
Date_Borrowed,
Library,
@.YourParameterHere
FROMLoans
WHEREBarcode = @.strBarcode
ANDLibrary = @.strLibrary

Best, Hugo
--

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

insert query results to "where in ()" query

Hello

I want to print 'delete from customers where id in (' + select id from persons+ ')'

It failes. How do I auto generate it to get the wanted resaults?

Thanks

Avi

I am not sure what you are trying here to achive.

If you want to print the string..

Print 'Delete from Customers Where Id in (Select Id from Persons)'

If you want to execte the query

Exec ('Delete From Customers Where Id in (Select Id from Persons)')

Ooops.. Its not clear.. Tell me more.. What you want to do..

|||

There is a delete command I need to do but I need to capture the current set of id. the same delete statment will not act the same a week ago.

I wan't to pring the query into a text file for futore rollback. the resault should be ' delete from X where id in (num1,num2,num3)'

|||

You are something looking for LOG Based deletion.

When you delete x rows you want to store it somewhere. Then later if you need you can revert back the changes..

Approach 1:

You can achive this using triggers.

Code Snippet

Create Table mydata (

[Id] int ,

[Name] Varchar(100)

);

Go

Create Table mylogdata (

[Id] int ,

[Name] Varchar(100) ,

[DeletedDatetime] datetime

);

Go

Create Trigger trg_MydataDeleteLoger

on Mydata For delete

as

Begin

Insert Into mylogdata

Select *,getdate() from Deleted;

End

Go

Insert Into mydata Values('1','Record1');

Insert Into mydata Values('2','Record2');

Insert Into mydata Values('3','Record3');

Insert Into mydata Values('4','Record4');

Insert Into mydata Values('5','Record5');

Go

Delete From Mydata Where Id<3

Select * From mydata

Select * From mylogdata

Approach 2:

Instead of deleting the rows use the IsDelete flag.

Code Snippet

Create Table mydata (

[Id] int ,

[Name] Varchar(100) ,

--attach the flag columns

IsDeleted bit Default 0,

DeletedDatetime datetime default null

);

|||

Isn't there a way to do it witha simple sql scritp?

|||

Reverting back your data after sometimes is not a simple task.

Your table may hold n numbers columns. Delete command is simply remove all the data.

So you should backup all the columns data before deleteing the row. Somewhere you have to keep that backup data

for future revert-back.

Trigger is one of the common way to achive this.. If you ask me, putting a IsDeleted flag is good practice too.

|||

All I want is to print the wanted string. isn't that possible? regardless of the cause or target. just print it.

Do I need to use cast or convert ?

Thanks

Avi

|||If

I want to print 'delete from customers where id in (' + select id from persons+ ')'

Then, the problem is handling the single quotes. To have a single quote in a PRINT string, use two single quotes. For example, the statement:

PRINT 'DELETE FROM Customers WHERE ID IN ('' + SELECT ID FROM Persons+ '')'

prints out:

DELETE FROM Customers WHERE ID IN (' + SELECT ID FROM Persons+ ')

|||

I would like to write

PRINT 'DELETE FROM Customers WHERE ID IN ('' + SELECT ID FROM Persons+ '')'

in the query analyzer and then press F5.

What I want to see in the resultpan is :

'DELETE FROM Customers WHERE ID IN (10,20,30)'

Can this be done ?

|||

No, a PRINT statement ONLY prints exactly what you tell it to print. It will not execute the statement [ SELECT ID FROM Persons ] and put the results in the PRINT output.

You could, however, 'build' the line you want printed as a variable [varchar()], and then print that variable. You would have to use a technique such as the one below to gather the output from the SELECT statement into a comma delimited list.

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
CustomerName varchar(20)
)

DECLARE @.MyList varchar(1000)

INSERT INTO @.MyTable VALUES( 'Smith' )
INSERT INTO @.MyTable VALUES( 'Williams' )
INSERT INTO @.MyTable VALUES( 'O''Reilly' )
INSERT INTO @.MyTable VALUES( 'Jones' )
INSERT INTO @.MyTable VALUES( 'Johnson' )
INSERT INTO @.MyTable VALUES( 'Marvin' )

SELECT DISTINCT
@.MyList = substring( ( SELECT ', ' + cast( RowID as varchar(10)) as [text()]
FROM @.MyTable t2
WHERE t2.CustomerName <> t1.CustomerName
FOR XML path(''), elements
), 3, 1000
)
FROM @.MyTable t1

DECLARE @.MyPrintStatement varchar(2000)

SET @.MyPrintStatement = '''DELETE FROM Customers WHERE ID IN (' + @.MyList + ')'''

PRINT @.MyPrintStatement

'DELETE FROM Customers WHERE ID IN (2, 3, 4, 5, 6)'

Insert query firing Insert & Update trigger at the same time.

Hello All,
I have a table on which I have created a insert,Update and a Delete trigger. All these triggers write a entry to another audit table with the unique key for each table and the timestamp.

Insert and Update trigger work fine when i have only one of them defined.

However when I have all the 3 triggers in place and when i try to fire a insert query on the statement. It triggers both insert and update trigger at the same time and has the same timestamp in the audit table.

Insert trigger goes as
CREATE TRIGGER InsRecord ON [dbo].[tableA]
AFTER INSERT
AS
insert Audit(change_id,change_table,change_type,date_chan ge)
select uniqueid, srctable,'Insert',GetDate() from inserted

Update trigger goes as
CREATE TRIGGER UpdRecord ON [dbo].[tableA]
FOR UPDATE
AS
insert Audit(change_id,change_table,change_type,date_chan ge)
select uniqueid, srctable,'Update',GetDate() from inserted

Delete Trigger goes as
CREATE TRIGGER delRecord ON [dbo].[tableA]
FOR DELETE
AS
insert Audit(change_id,change_table,change_type,date_chan ge)
select uniqueid, srctable,'Delete',GetDate() from deleted

Note:This tableA has relations with 2 other tables on 1 field each from each table but i don't think it should matter.

Please advise how to prevent it.CREATE TRIGGER alteredRecord ON [dbo].[tableA]
FOR INSERT, UPDATE, DELETE
AS
BEGIN

...declare lngIns & lngDel

SELECT lngIns=count(col1)
from inserted

select lngDel=count(col1)
from deleted

IF lngIns>0 and lngDel=0
...inserted
else if lngIns>0 and lngDel>0
...updated
else if lngIns=0 and lngDel>0
...deleted
end

END

insert query ?

my application will add and delete and update records in db

my problem is when to insert

I have one text box and one dropdownbox one to write the name of db and the dropdownbox to choose the holding server ..

this is the structure of each table >>

servers_tbl : SRV_ID,Server_Name

DB_tbl : DB_ID,DB_Name

srvdb_tbl : DB_ID,SRV_ID(forign keys from the previous tables)

so >>>

I want to add a new db to a server

so I am writing the new db name in the textbox and choose the server from the dropdownbox and press a button to add the db name in the DB_tbl.DB_Name and add the db id in the DB_tbl.DB_ID to the srvdb_tbl.DB_ID and server id in the Servers_tbl.SRV_ID

any one can help me ...

You need a stored procedure along the lines of

CREATE PROCEDURE dbo.AddDbServer ( @.DB_Name VARCHAR(50), @.SRV_ID INT) AS

DECLARE @.DB_ID INT

IF NOT EXISTS(SELECT * FROMDB_tbl WHERE DB_NAME = @.DB_NAME)

BEGIN INSERT INTO DB_tbl (DB_NAME) VALUES (@.DB_Name)

SELECT @.DB_ID = SCOPE_IDENTITY

END

ELSE

SELECT @.DB_ID = SELECT DB_ID FROMDB_tbl WHERE DB_NAME =@.DB_Name

END

INSERT INTOsrvdb_tbl(DB_ID,SRV_ID) VALUES (@.DB_ID , @.SRV_ID)

You will need to test the stored procedure before incorporating it into your program.

insert query

Plz send me insert, delete stored procedure ... very urgent.....

Quote:

Originally Posted by palanivel

Plz send me insert, delete stored procedure ... very urgent.....

create procedure <Procedure Name> (<@.Parameter1> <DataType>, <@.Parameter2> <DataType>,...etc)

begin
what ever insert, update, delete qry here
end|||

Quote:

Originally Posted by palanivel

Plz send me insert, delete stored procedure ... very urgent.....

create procedure <procedure name>

drop procedure <procedure name>

Insert operation was discarded in subscriber(Newbie)

I have some subscribers in cluster.
when delete a record in a subscriber side, Publisher accepted the
operation.However if try to insert
a record with the same rowguid in the same table named "Image",Publisher
rejected the operation.
After tracing that operations of the table Image,the record was inserted
into the table. and then
the record was deleted.
checking the publisher...
I found there is a record in the table conflict_mergeRepPub_Image.It is my
insert record.
Other fields list as below:
Conflict type 3
Reason Code 3
Reason Text The same row was updated at 'CR950A24G.DDS' and deleted
at 'CR165.DDS'.
The resolver chose the deletion as the winner.
'CR950A24G.DDS'is the subscribe and 'CR165.DDS' is publisher.
Our publisher use default resolver.I had tried to change the resolver to
"subscriber always win",
But it still happened. And I had doubted the FK on the table. But after I
delete the FK, issue is
still on.
I am comer.I wish I had described the complex issue clearly.
This is a primary key conflict. You are trying to enter two rows (one on the
publisher and one on the subscriber) with the same rowguid value. It will
always be kicked back. You need to use a different value on either side if
you wish it to remain in.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kaliven" <Graduate@.163.com> wrote in message
news:%233vLb$oSHHA.920@.TK2MSFTNGP05.phx.gbl...
> I have some subscribers in cluster.
> when delete a record in a subscriber side, Publisher accepted the
> operation.However if try to insert
> a record with the same rowguid in the same table named "Image",Publisher
> rejected the operation.
> After tracing that operations of the table Image,the record was inserted
> into the table. and then
> the record was deleted.
> checking the publisher...
> I found there is a record in the table conflict_mergeRepPub_Image.It is my
> insert record.
> Other fields list as below:
> Conflict type 3
> Reason Code 3
> Reason Text The same row was updated at 'CR950A24G.DDS' and
> deleted at 'CR165.DDS'.
> The resolver chose the deletion as the winner.
> 'CR950A24G.DDS'is the subscribe and 'CR165.DDS' is publisher.
> Our publisher use default resolver.I had tried to change the resolver to
> "subscriber always win",
> But it still happened. And I had doubted the FK on the table. But after I
> delete the FK, issue is
> still on.
> I am comer.I wish I had described the complex issue clearly.
>
|||Hi Hilary,
Thanks for your answer.
I take a test to remove the PK and insert a row.It works all right.It seems
to your instruction is right.But I can not understand why there are two rows
to be inserted. I just take the action to insert one row in subscriber. In
my logical I need insert the row into the table. Any suggestion for me?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OFgs$ZqSHHA.1036@.TK2MSFTNGP03.phx.gbl...
> This is a primary key conflict. You are trying to enter two rows (one on
> the publisher and one on the subscriber) with the same rowguid value. It
> will always be kicked back. You need to use a different value on either
> side if you wish it to remain in.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Kaliven" <Graduate@.163.com> wrote in message
> news:%233vLb$oSHHA.920@.TK2MSFTNGP05.phx.gbl...
>
|||Hi Hilary,
Thanks for your answer.
I take a test to remove the PK and insert a row.It works all right.It seems
to your instruction is right.But I can not understand why there are two rows
to be inserted. I just take the action to insert one row in subscriber. In
my logical I need insert the row into the table. Any suggestion for me?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OFgs$ZqSHHA.1036@.TK2MSFTNGP03.phx.gbl...
> This is a primary key conflict. You are trying to enter two rows (one on
> the publisher and one on the subscriber) with the same rowguid value. It
> will always be kicked back. You need to use a different value on either
> side if you wish it to remain in.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Kaliven" <Graduate@.163.com> wrote in message
> news:%233vLb$oSHHA.920@.TK2MSFTNGP05.phx.gbl...
>