Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net have generously sponsored dedicated servers to ensure a reliable and scalable dedicated hosting solution for BigResource.com.





TRIGGER FOR INSERT,UPDATE,DELETE


I HAVE TWO TABLES IN THE DATABSE AND THE SECOND TALE SI FOR AUDITING.
I WANT CREATE THE TRIGGER ON FIRST TABLE SO THAT I CAN PUT THE STATUS
LIKE INSERT,UPDATE OR DELETE IN THE STATUS COLUMN IN SECOND TABLE.
CAN SOMEBODY HELP IN WRITING THAT TRIGGER..?
HOW CAN I DETERMINE WAETHER THE RECORD IS BEEN INSERTED OR UPDATED OR DELETED.

DO I HAVE TO WRITE A SEPERATE TRIGGER FOR EACH ACTIVITY..OR I CAN WRITE IT IN THE
SINGLE TRIGGER..?

PLEASE SUGGEST ME..ITS URGENT.

THANKS IN ADVANCE
HARISH




View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
How Create Trigger Stop Update Delete And Insert
How to create trigger to stop the delete , updation and insert in the table of database ....

How can i stopped .......................I want to apply on whole table of database

Pls help me out.

Yaman

View Replies !   View Related
VS2005 - Using Detailsview To Update, Insert And Delete Rows From SQL 2005 Database. Delete And Insert Work But Update Does Not - No Errors Returned
  Using VS 2005 DetailsView to insert, delete, and update rows in SQL 2005 database.  insert and delete work but update does not.  I recieve no errors and the detailsView comes back unchanged (as well as table row is unchanged).   I am trying to use as little code behind as possible. However I do have ItemUpdating routines that seem to work (i.e Checking table for new login duplicates and encrypting passwords).  The following is the source code generated by VS2005:<%@ Page Language="VB" AutoEventWireup="false" CodeFile="frmDbRegionMgrNew.aspx.vb" Inherits="frmDbRegionMgrNew" Title="Region Manager DB Update" Theme="detailsVeiwTheme" %><%@ Import Namespace="System.Data" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"><title>Untitled Page</title> </head><body><form id="form1" runat="server"><div><asp:Label ID="Label1" runat="server" Font-Bold="True" Font-Size="Large" ForeColor="#004000"Style="z-index: 101; left: 14px; position: absolute; top: 10px" Width="199px">Data Base Maintenance </asp:Label><asp:HyperLink ID="HyperLink1" runat="server" BackColor="ActiveBorder" BorderColor="ActiveBorder"BorderStyle="Outset" Font-Bold="True" Font-Size="X-Small" ForeColor="#004000"Height="31px" NavigateUrl="DataBaseMaint.aspx" Style="z-index: 133; left: 524px;position: absolute; top: 7px" Width="96px">DB Main Menu</asp:HyperLink><br /><br /><br /><table style="width: 654px"><tr><td style="width: 120px"><asp:Label ID="Label2" runat="server" Font-Bold="True" Font-Size="Large" ForeColor="#004000"Text="Regional Manager" Width="189px"></asp:Label></td><td style="width: 100px"></td><td style="width: 203px"></td></tr><tr><td style="width: 120px" valign="top"><asp:Label ID="Label3" runat="server" Font-Bold="True" Font-Size="Small" ForeColor="#004000"Style="z-index: 128; left: 2px; position: absolute; top: 115px" Width="128px">Select Greenhouse -></asp:Label></td><td style="width: 100px" valign="top"><asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource1"DataTextField="Name" DataValueField="GrnHseID"></asp:DropDownList><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MetrolinadataConnectionString %>"SelectCommand="SELECT [GrnHseID], [Name] FROM [Greenhouse]"></asp:SqlDataSource></td><td style="width: 203px">&nbsp;<asp:DetailsView ID="DetailsView1" runat="server" AllowPaging="True" AutoGenerateRows="False"DataKeyNames="RegionMgrID" DataSourceID="SqlDataSource2" Height="50px" Width="125px"><Fields><asp:TemplateField HeaderText="RegionMgrID" InsertVisible="False" SortExpression="RegionMgrID"><EditItemTemplate><asp:Label ID="Label1" runat="server" Text='<%# Eval("RegionMgrID") %>'></asp:Label></EditItemTemplate><ItemTemplate><asp:Label ID="Label1" runat="server" Text='<%# Bind("RegionMgrID") %>'></asp:Label></ItemTemplate></asp:TemplateField><asp:TemplateField HeaderText="GrnHseID" SortExpression="GrnHseID"><EditItemTemplate><asp:Label ID="Label2" runat="server" Text='<%# Eval("GrnHseID") %>'></asp:Label></EditItemTemplate><InsertItemTemplate><asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("GrnHseID") %>'></asp:TextBox></InsertItemTemplate><ItemTemplate><asp:Label ID="Label2" runat="server" Text='<%# Bind("GrnHseID") %>'></asp:Label></ItemTemplate></asp:TemplateField><asp:BoundField DataField="DispLvl" HeaderText="DispLvl" SortExpression="DispLvl" /><asp:BoundField DataField="FName" HeaderText="FName" SortExpression="FName" /><asp:BoundField DataField="Minit" HeaderText="Minit" SortExpression="Minit" /><asp:BoundField DataField="LName" HeaderText="LName" SortExpression="LName" /><asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" /><asp:BoundField DataField="Ext" HeaderText="Ext" SortExpression="Ext" /><asp:BoundField DataField="Cell" HeaderText="Cell" SortExpression="Cell" /><asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" /><asp:BoundField DataField="Login" HeaderText="Login" SortExpression="Login" /><asp:BoundField DataField="Password" HeaderText="Password" SortExpression="Password" /><asp:BoundField DataField="AccessLvl" HeaderText="AccessLvl" SortExpression="AccessLvl" /><asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" /></Fields></asp:DetailsView><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConflictDetection="CompareAllValues"ConnectionString="<%$ ConnectionStrings:MetrolinadataConnectionString %>" DeleteCommand="DELETE FROM [RegionMgr] WHERE [RegionMgrID] = @original_RegionMgrID AND [GrnHseID] = @original_GrnHseID AND [DispLvl] = @original_DispLvl AND [FName] = @original_FName AND [Minit] = @original_Minit AND [LName] = @original_LName AND [Phone] = @original_Phone AND [Ext] = @original_Ext AND [Cell] = @original_Cell AND = @original_Email AND [Login] = @original_Login AND [Password] = @original_Password AND [AccessLvl] = @original_AccessLvl"InsertCommand="INSERT INTO [RegionMgr] ([GrnHseID], [DispLvl], [FName], [Minit], [LName], [Phone], [Ext], [Cell], , [Login], [Password], [AccessLvl]) VALUES (@GrnHseID, @DispLvl, @FName, @Minit, @LName, @Phone, @Ext, @Cell, @Email, @Login, @Password, @AccessLvl)"OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [RegionMgr] WHERE ([GrnHseID] = @GrnHseID)"UpdateCommand="UPDATE [RegionMgr] SET [GrnHseID] = @GrnHseID, [DispLvl] = @DispLvl, [FName] = @FName, [Minit] = @Minit, [LName] = @LName, [Phone] = @Phone, [Ext] = @Ext, [Cell] = @Cell, = @Email, [Login] = @Login, [Password] = @Password, [AccessLvl] = @AccessLvl WHERE [RegionMgrID] = @original_RegionMgrID AND [GrnHseID] = @original_GrnHseID AND [DispLvl] = @original_DispLvl AND [FName] = @original_FName AND [Minit] = @original_Minit AND [LName] = @original_LName AND [Phone] = @original_Phone AND [Ext] = @original_Ext AND [Cell] = @original_Cell AND = @original_Email AND [Login] = @original_Login AND [Password] = @original_Password AND [AccessLvl] = @original_AccessLvl"><DeleteParameters><asp:Parameter Name="original_RegionMgrID" Type="Int32" /><asp:Parameter Name="original_GrnHseID" Type="Int32" /><asp:Parameter Name="original_DispLvl" Type="Int32" /><asp:Parameter Name="original_FName" Type="String" /><asp:Parameter Name="original_Minit" Type="String" /><asp:Parameter Name="original_LName" Type="String" /><asp:Parameter Name="original_Phone" Type="String" /><asp:Parameter Name="original_Ext" Type="String" /><asp:Parameter Name="original_Cell" Type="String" /><asp:Parameter Name="original_Email" Type="String" /><asp:Parameter Name="original_Login" Type="String" /><asp:Parameter Name="original_Password" Type="String" /><asp:Parameter Name="original_AccessLvl" Type="Int32" /></DeleteParameters><UpdateParameters><asp:Parameter Name="GrnHseID" Type="Int32" /><asp:Parameter Name="DispLvl" Type="Int32" /><asp:Parameter Name="FName" Type="String" /><asp:Parameter Name="Minit" Type="String" /><asp:Parameter Name="LName" Type="String" /><asp:Parameter Name="Phone" Type="String" /><asp:Parameter Name="Ext" Type="String" /><asp:Parameter Name="Cell" Type="String" /><asp:Parameter Name="Email" Type="String" /><asp:Parameter Name="Login" Type="String" /><asp:Parameter Name="Password" Type="String" /><asp:Parameter Name="AccessLvl" Type="Int32" /><asp:Parameter Name="original_RegionMgrID" Type="Int32" /><asp:Parameter Name="original_GrnHseID" Type="Int32" /><asp:Parameter Name="original_DispLvl" Type="Int32" /><asp:Parameter Name="original_FName" Type="String" /><asp:Parameter Name="original_Minit" Type="String" /><asp:Parameter Name="original_LName" Type="String" /><asp:Parameter Name="original_Phone" Type="String" /><asp:Parameter Name="original_Ext" Type="String" /><asp:Parameter Name="original_Cell" Type="String" /><asp:Parameter Name="original_Email" Type="String" /><asp:Parameter Name="original_Login" Type="String" /><asp:Parameter Name="original_Password" Type="String" /><asp:Parameter Name="original_AccessLvl" Type="Int32" /></UpdateParameters><SelectParameters><asp:ControlParameter ControlID="DropDownList1" Name="GrnHseID" PropertyName="SelectedValue"Type="Int32" /></SelectParameters><InsertParameters><asp:Parameter Name="GrnHseID" Type="Int32" /><asp:Parameter Name="DispLvl" Type="Int32" /><asp:Parameter Name="FName" Type="String" /><asp:Parameter Name="Minit" Type="String" /><asp:Parameter Name="LName" Type="String" /><asp:Parameter Name="Phone" Type="String" /><asp:Parameter Name="Ext" Type="String" /><asp:Parameter Name="Cell" Type="String" /><asp:Parameter Name="Email" Type="String" /><asp:Parameter Name="Login" Type="String" /><asp:Parameter Name="Password" Type="String" /><asp:Parameter Name="AccessLvl" Type="Int32" /></InsertParameters></asp:SqlDataSource>&nbsp; &nbsp; &nbsp;&nbsp;</td></tr><tr><td style="width: 120px"></td><td style="width: 100px"></td><td style="width: 203px"></td></tr></table></div></form></body></html>Appreciate any and all help!Dave

View Replies !   View Related
CLR Trigger -&&> Send Insert/Update/Delete Message To Windows Service
Hi,

I have an c# windows service, which is running on the same machine where my mssql server is installed. This service must be informed for each insert/update/delete event on one specific table.

My idea was to create an CLR Trigger for this table which can communicate with this service via .NET remoting. But the problem is, that the system.runtime.remoting assembly is not integrated within the mssql clr enviroment => i can't use remoting.

Are there any other idea's how can i solve this?

Best regards,
Thomas

View Replies !   View Related
DELETE And UPDATE Trigger Question
HelloI have a Trigger on a table. Here is the code  ALTER TRIGGER [dbo].[OnOrderDelete]
ON [dbo].[orders]
AFTER DELETE,UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ids int;
SELECT @ids =( SELECT id from DELETED);
DELETE FROM files WHERE OrderId = @ids;
ENDActually the UPDATE event handler is not wanted here, but why when I leave him I have a following behaviour:When orders table is updated, the "SELECT @ids =( SELECT id from DELETED);
DELETE FROM files WHERE OrderId = @ids;" part is executed, and the program recognizes DELETED as UPDATED! (Like " SELECT @ids =( SELECT id from UPDATED) ") Is this right? And how can I part UPDATED and DELETED ?ThanksArtashes

View Replies !   View Related
Create One Trigger For Both Update And Delete
hi,CAn i have one trigger for both Update and DeleteDelete Trigger---------------------create Trigger [tr_delete_user_log]on [dbo].[user_log] for deleteasbegininsert into z_user_log select * from deletedendTrigger Update---------------------CREATE Trigger [tr_update_user_log]on [dbo].[user_log] for updateasbegininsert into z_user_log select * from deletedendCan i have one trigger instead of these Triggers ..

View Replies !   View Related
Trigger For Delete Or Specific Update
I have a table where I want to prevent user from deleting or setting a flag on a field to "y" with a database trigger (sql 2000). I understand the trigger for just one (stopping the delete, or stopping the field being changed to "y"). Should I have 2 seperate triggers or would there be a way to handle both.

View Replies !   View Related
Creating A Delete/insert Trigger
I am trying to create a trriger.
My DB has two admin users. Only user who has a admin right can hove right to either delete or insert a record on contract table.
when they delete or insert a record on contract table, it iwll generate the modification information which is user name , contractNum, date and time.

My first question is do i need to have the modification table to store that info for that trigger or can I just generate a view?
Second question is code for that trigger will be?

My contract DDL is
create table contract(
contractNum int identity(1,1) primary key,
contractDate smalldatetime not null,
tuition money not null,
studentId char(4) not null foreign key references student (studentId),
contactId int not null foreign key references contact (contactId)
);


thanks for your help in advance.
gazawaymy

View Replies !   View Related
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
GO

View Replies !   View Related
Question About Trigger Insert Before Delete
Hello, everybody
 
 I have a question about trigger.
I have a table called "Users", it has a ID (auto increment), UserName, FirstName...ect.
The question is if one row is deleted, I want to put this row into "Users_Histroy" Table whose structure is the same as "Users" Table and then delete this row in "Users" Table..
How can I achieve this? I am pretty new about triggers.
Thank you very much.

View Replies !   View Related
Trigger On Insert To Delete Record
Can anybody help me in writing a SQl trigger to delete a record when inserted after 70 seconds.?

View Replies !   View Related
How To Write A Trigger Restict Delete Or Update
hi All

I have an administrator account in my users table, I want to restrict user delete or update (modify) this account, how can I write a trigger to do that ?

Here is missing my statement :


CREATE TRIGGER UnModify_Admin_Account
ON Users
AFTER DELETE,UPDATE
AS
BEGIN
SET NOCOUNT ON;
// if username is 'Administrator' RAISERROR and return @@ERROR
END
GO


Many Thanks in advance !

Regards,
QuachNguyen

View Replies !   View Related
Using Trigger/Stored Procedure (Delete, Insert)?
I have 3 tables...TableA, TableB, TableC TableA - Personal InformationPersonalInfoId (Primary) , First Name,Last NameTableB - Personal Information To Department IDReferenceID, FKPersonalInfoId, FKDepartmentIdTableC - DepartmentDepartmentId, DepartmentNameI am coding Asp.Net VB using VWD express with Sql Server Express.  I know how to create a stored procedure to delete, insert and even update a record in TableA, TableB, TableC respectively.If I need to delete a record in TableC, which has a related record in TableB, I have read that I need to use a Trigger.  I never have used a Trigger and it is new to me.  Can someone point me a way on how to use one in this case of my deleting scenario.  Pretty much, if a user clicks on a delete button, and deletes a record in my TableC, I dont want a  FKDpartmentId in my TableB that doesnt exist anymore because it was deleted in TableC or prevent a user from deleting that record till the relationship in TableB is no longer valid. In the same vain, If I have a input form which ask the user to enter their First Name and Last Name and Department, i would like to add those records in TableA for First and Last Name, TableB for the Department.  Once again, how do I create a Trigger that if I insert a record in Table A to also insert the information for Department in Table B, if its successful in my stored procedure.  Hope that made sense.Thanks.   

View Replies !   View Related
Create Delete Trigger On Table1 To Update A Filed On Table2
Hi everyoneI am trying to create a DELETE Trigger. I have 2 tables. Table1 andTable2. Table 2 has all the same fields and records as Table1 + 1extra column "date_removed"I would like that when a record is deleted from Table 1, the triggerfinds that record in Table2 and updates the date_removed filed withcurrent time stamp.The primary key on both is combination of domain,admin_group and cn.CREATE TRIGGER [tr_updateTable2] ON [dbo].tbl_Table1FOR DELETEASUpdate Table2SET date_removed = getDate()I'm stuck here, how do I manipulate on Table2 only the records thatwere deleted on Table1, so to only update date_removed filed for themin Table2?I guess i need to compare domain, cn and admin_group, but I don't knowhow.Any help would be greatly appreciatedThanks! :-)

View Replies !   View Related
Trigger To Update A Table On Insert Or Update
 

Hello
 
I've to write an trigger for the following action
 
When a entry is done in the table Adoscat79 having in the index field Statut_tiers the valeur 1 and a date in data_cloture for a customer xyz
 
all the entries in the same table where the no_tiers is the same as the one entered (many entriers) should have those both field updated
 
statut_tiers to 1
and date_cloture to the same date as entered
 
the same action has to be done when an update is done and the valeur is set to 1 for the statut_tiers and a date entered in the field date_clture
 
thank you for your help
I've never done a trigger before
 

View Replies !   View Related
Update Or Delete With Insert?
Hi All,
I have this project I'm working on it's Product Content Management System rewrite. I got to the point of updating the Product By Sku and not sure if I should use UPDATE statement or I should DELETE sections assosiated with the ProductContentID and then re-insert them again? I'm not sure which is more afficient?
I can really do both and it's really not that complicated, the only problem I see with DELETE then INSERT is the ProductContentSectionId in the Sections table is going to grow very fast and I'm a bit concerned about it.
We use SQL Server 2000 and we have about 4 bound tables where we keep the data. The one I'm talking about is the sections table where we keep the actual types of product content like a BoxShot, Description, Key Features and so on...
Thank you in advance!
Tatyana Hughes
 

View Replies !   View Related
Update, Delete, Insert
I am trying to update a SQL database with data from a Wang system. The Wang data is dumped to a txt file. I then import it into an update table in SQL via Access. Some of the data is new and some of the data is updated records. At this point I have been trying to create a script to update and add data to a table via the query tool in SQL Then delete data from the update table.

I was able to get the UPDATE and DELETE to work but I have not figured out how to insert new records at the same time? Can I use an IF statement? I will apreciate any help or sample code, Thanks.

UPDATE MemberList
Set Name = NameUpd, Address1 = Address1Upd, Address2 = Address2Upd, City = CityUpd, State = StateUpd, ZipCode = ZipCodeUpd, MemberStatus = MemberstatusUpd
FROM MemberList, MemberListUpd
WHERE MemberList.MemberNumber = MemberListUpd.MemberNumberUpd

INSERT ?

DELETE MemberListUpd

View Replies !   View Related
Use Insert Update Delete In DTS
I have an Access table from which I have to get data periodically in 30
minutes everyday to an SQL server table. both have the same data structure. I
want to get all the new records as insert, modified records as update and
deleted records in source table that still exists in target table to be
deleted. I have tried searching on the net. They all explain about using the
insert, update and delete statements in DDQ. I understand that but how do i
change the related constant in the ActiveX script for eg: Main =
DTSTransformstat_UpdateQuery for update. I saw somewhere an eg. where i use a
select case according to some column in the table that has to be changed to
insert or update but i dont want to add a new column for this. some solution
that will compare the source table and target table with sql queries using
exists. but i cant identify target and source in a query if i use the
"Execute SQL task". Can someone help.

View Replies !   View Related
Update = Delete &&amp; Insert?
I vaguely recall reading an article that I can no longer find that an update statement is executed as a combination of a Delete and an Insert by SQL server.  Does anyone know if this a still a true statement in SQL Server 2005? 

Thanks,

-shl

View Replies !   View Related
Delete/insert Vs Update
Im trying to keep a mirror image of some data Im getting from Quickbooks.

As the records are inserted into the database I need to check if a record exists and either update or insert a new one.

it seems easier just to delete using the tnxid and reinsert vs updating

my question is if I go

begin

INSERT INTO QBInvoicesQue(100s of feilds)

end


begin

delete from QBInvoices where txnid = @TxnID

end

and there is not matching txnid to delete from will it cause any problems? before going to the insert statement?


begin

INSERT INTO QBInvoices(100s of feilds)

end

View Replies !   View Related
Can't Update, Insert, Or Delete Rows
I have recently started an ASP.Net application and am having some issues updating, inserting and deleting rows. When I started working with it, I was getting errors because it could not find any update command. Eventually, I figured out how to automatically generate the commands, by configuring my SQLDataSource control and clicking the "advanced" button. Right now though, I have generated the commands, but I still can not insert, update or delete rows. When I attempt to update anything, I recieve an error that says "The data types text and nvarchar are incompatible in the equal to operator." Nowhere in my table do I have any rows that use the datatype "nvarchar", only "text" and "int". I tried switching all of my text columns to "nvarchar(500)", which did not help. I am led to believe that the auto generated SQL procedures are trying to do something behind the scenes that is making my database act up, because even when I delete rows, I get the same exception, so the datatypes cannot be messed up there, because all that the datasource is doing is deleting rows, therefore there is no need to worry about data types. I only get the error when I check the "Use optimistic concurrency" box. When I do not use optimistic concurrency, I can delete, insert, and update rows... but nothing happens. There are no errors, but nothing is deleted, updated or inserted either. Upon postback, nothing has changed. I may upload a copy of the exact exception page, if someone thinks that it may help. Here is the update command that was generated: UPDATE [Record Information] SET [Speed] = @Speed, [Recording Company] = @Recording_Company, [Year] = @Year, [Artist] = @Artist, [Side 1 Track Title] = @Side_1_Track_Title, [Side 1 Track Duration] = @Side_1_Track_Duration, [Side 2 Track Title] = @Side_2_Track_Title, [Side 2 Track Duration] = @Side_2_Track_Duration, [Sleeve Description] = @Sleeve_Description WHERE [Record Database ID] = @original_Record_Database_ID
Apparently no stored procedures exist for any of these operations, and I am unsure why. The "Record Database ID" is my identity column, and is the only field that is (and is supposed to be) uneditable.

View Replies !   View Related
Best Practices For Insert/Update/Delete
 for now, doing a small school project, i find doing SPs for Insert useful, like checking for existing data and not inserting, that might not be the best method, i had advice from here i can use unique constraints instead, then what about update and delete? SPs also? the pros make SPs for everything? currently use dynamically generated SQL from SqlDataSources. for Update / delete. some delete are SPs too...

View Replies !   View Related
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!!

View Replies !   View Related
SqlDataSource: Insert, Update And Delete Not Available
Hello Guys,
 
I’m trying to create a sqlDataSource using the wizard, I can get the select statement, the here clause and the order by clause but when I click on the Advanced button the options to generate the insert, update and delete statements are not available. Does anyone know what am I doing wrong?

View Replies !   View Related
SQL Update/Insert/Delete Problem
Hi,I just upgraded my ASP.NET 2.0 BETA 2 environment to the final release of ASP.NET 2.0 VWD.Once the update was finished, I could open my website without any problems..... Now, I noticed that in the final release, some modifications have been included in the Membership Stored Procedure and other stored procedures. So I created a new database (SQL Express) and added my data again.After re-creating my SQLDataSources, I tryed to enable the Editing and Deleting option in VWD and once I run my web application, it seems when selecting editing and then update, it doesn't work anymore....This is my code :
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:IMMOASPNETDBConnectionString %>"
DeleteCommand="DELETE FROM aspnet_test WHERE (testID = @Original_testID)" SelectCommand="SELECT BuyID, BuyNL, BuyFR, Lastupdated FROM aspnet_Buy"
UpdateCommand="UPDATE aspnet_Buy SET BuyNL = @BuyNL, BuyFR = @BuyFR WHERE (BuyNL = @original_BuyID)">
<DeleteParameters>
<asp:Parameter Name="Original_testID" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="BuyNL" />
<asp:Parameter Name="BuyFR" />
<asp:Parameter Name="original_BuyID" />
</UpdateParameters>
</asp:SqlDataSource>
&nbsp;<br />
&nbsp;<br />
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False"
DataKeyNames="BuyID" DataSourceID="SqlDataSource1">
<Columns>
<asp:CommandField ShowEditButton="True" />
<asp:BoundField DataField="BuyID" HeaderText="BuyID" InsertVisible="False" ReadOnly="True"
SortExpression="BuyID" />
<asp:BoundField DataField="BuyNL" HeaderText="BuyNL" SortExpression="BuyNL" />
<asp:BoundField DataField="BuyFR" HeaderText="BuyFR" SortExpression="BuyFR" />
<asp:BoundField DataField="Lastupdated" HeaderText="Lastupdated" SortExpression="Lastupdated" />
</Columns>
</asp:GridView>Can someone help me with this ? What is wrong with the Update command ?Thanks to all,Bart

View Replies !   View Related
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).

View Replies !   View Related
Triggers (insert Update Delete)
hi, is there a difference when I combine all 3 above triggers in one code vs putting code in 3 different triggers.

for exmple,
create trigger tr_name
for insert, update, delete

as ... code
vs

writting codein 3 separate triggers


Thanks
AAA

View Replies !   View Related
What Is Faster?! Update Or Delete And Insert
Hello,

What is faster / better for performance?

Check if something already excist and update the changed values? or delete the whole table and then insert everything?

Thanks!

View Replies !   View Related
SSIS Delete/Insert/Update
I am still learning SSIS.

What is the best way to do delete/insert/update in a database in SSIS. I am looking to achieve something similar to what was there in a Data Driven Query in SQL Server 2000. I would like to delete/insert/update the destination table based on the condition that my lookuptable returns

For ex

If "the user in my source table has changed the status"  Then

 Update the user row in the destination table

Else skip the row

If "the user in my source table has delete flag =1 "  Then

Delete the user in the destination table

Else skip the row

If "the user in my source table is new(has a new ID)  "  Then

Insert the user in the destination table

Else skip the row

 

 

 

Any suggestions ....

Cheers

Siaj

 

View Replies !   View Related
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

 

 

 

 

View Replies !   View Related
Linq To SQL And Generate Insert/Update/Delete SP
Hi All
Is it possible to use linq to sql to generate the insert/ update /delete sp? Same as some other ORM tools?
regards
Alex

View Replies !   View Related
My SQLDataSource Select/Update/Insert/Delete
hi
i have form view that retrieves a single row
i dont want to use SQLDataSource default Select/Update/Insert/Delete buttons
i am using Stored Procedures
I want to have my own buttons, the select has parameters, how to retrieve data for that parameter?
 

View Replies !   View Related
SqlDataSource Not Allowing Insert, Update And Delete
i have default.aspx file and i put SqlDataSource into my designer form.when i want to configure SqlDataSource it not allowed my to insert, update and delete only insert is allowed.what is the problem and the solution for this case ...thx

View Replies !   View Related
Lookup Table Insert, Update, And Delete...
All,

Just wondering if anyone is aware of a SQL server shareware utility that places a front end on a table to manage insert, update, and delete of rows on a lookup table.

We can certainly write this but before reinventing the wheel I figure I'd ask and see.

Many Thanks,

Isaac

View Replies !   View Related
Transactional Replication - UPDATE (Delete/Insert)
Hi,

Any updates on tables at publisher are being converted to Delete/Insert pair
at subscriber end with transactional replication. Our business requirement is not to delete data from few tables and also to get rid of Foreing Key
Constraint error.

How to avoid this Deletes during Update? Any ideas about writing custom stored procs?

Thanks

View Replies !   View Related
Attn: Marcus Update - Delete/Insert
Marcus,

If the update happens as Delete/Insert in local machine,
Why the Foreign Key constraint error doesn't happen on Local?

The problem is we are trying to update Parent table , it works fine.
Because Replication is using Delete/Insert for updates, deleting the
PArent record with
child records gives an error.

Thanks

View Replies !   View Related
Writing Insert, Update And Delete Commands.
I am new developer in SQL. I have tried several combinations of insert etc. but  that doesn't work.
 

I need one proc which has to do an update/insert/delete in two tables as per the requirement mentioned below.

Tables:
=======
tFeatureAccess
tModuleMainAccess


Table Scripts:
==============

CREATE TABLE tFeatureAccess(intModuleId INT,intFeatureId INT,intAccessLevelId INT,intRoleId INT,strRequestStatus VARCHAR(20), strId VARCHAR(20),strVersionAliasId VARCHAR(20))
create clustered index temp_clustered_index1 on tFeatureAccess(strId,intRoleId,intFeatureId,intModuleId,intAccessLevelId,strVersionAliasId)

Note : strRequestStatus can be only either 'A' or 'P'


CREATE TABLE tModuleMainAccess(strId VARCHAR(20),intAccessLevelId INT,intModuleId INT,strVersionAliasId VARCHAR(20),strIsApproved VARCHAR(20))
create clustered index temp_clustered_index2 on tModuleMainAccess(strId,intAccessLevelId,intModuleId,strVersionAliasId)

Note : strIsApproved can be either '1' or '0'

 

I have data coming in from a XML string and after parsing it, I am storing it in a temp table called #temp.

The values are as below:


CREATE TABLE #TEMP
(ModuleId INT,
FeatureID INT,
Access INT,
MainAccessLevelID INT,
IsApproved VARCHAR(20))


INSERT INTO #TEMP
VALUES(5,11,3,5,0)

INSERT INTO #TEMP
VALUES(5,12,3,5,0)

INSERT INTO #TEMP
VALUES(5,13,3,5,0)

INSERT INTO #TEMP
VALUES(5,43,3,5,0)

INSERT INTO #TEMP
VALUES(2,5,1,6,0)

INSERT INTO #TEMP
VALUES(2,6,3,6,0)


There is no PK on this temp table.

Note : ModuleId corresponds to intModuleId, FeatureID corresponds to intFeatureId, Access corresponds to intAccessLevelId,MainAccessLevelID corresponds to intRoleId and IsApproved corresponds to strIsApproved

 

Here is the requirement:
========================
Input Parameters : @strId ,@strVersionAliasId, XML string containing values for intModuleId,intFeatureId,intAccessLevelId,intRoleId,strRequestStatus. The XML string values are then parsed and stored in the temp table.

PART I : For table tFeatureAccess

(a) For a given strId ,strVersionAliasId and strRequestStatus and if some changes are there in tFeatureAccess for intAccessLevelId,intRoleId  from what we have in the temp table then update them and change the strRequestStatus from 'P' to 'A'

(b) If the strRequestStatus is 'A' in tFeatureAccess when comparing to the values in the temp table for intModuleId,intFeatureId, then simply update the rows for intAccessLevelId,intRoleId.

(c) else if ModuleId,FeatureId available in the temp table does not match with intModuleId,intFeatureId in tFeatureAccess then insert into tFeatureAccess with strRequestStatus as 'A'


PART II : For table tModuleMainAccess

Note this is same as above, but instead of strRequestStatus we have strIsApproved and can be either '1' or '0'.

 


 

View Replies !   View Related
Update , Insert ,delete Option From The Database ? Help
Hello,

 

Where can i give on the sql server Management studio that i can insert and update, delete.

Thenks for you help

View Replies !   View Related
Identifying A Table Update / Insert / Delete
How to find out that a table has changed. For example if a table has 50K rows, and if any update, insert, or delete was made it should be captured without using any trigger. Is it possible to get such information from any of the system table or DMVs?

View Replies !   View Related
Insert, Update, Delete Rows In A VIEW ....
Hello,

Is it possible to insert rows into a view, update rows in a view and delete rows in a view?
If so, how does T-SQL handle it when a VIEW is the result of following query?

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2 ON table_name1.col = table_name2.col
WHERE condition

Thanks in advance,
Erke.

View Replies !   View Related
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.

View Replies !   View Related
How To Update,delete,insert The Data Into Xml Colu
Hi all,

How to update a particular value in xml file which was loaded into sql server 2005 database
which is of xml-type

How to DELETE a particular value in xml file which was loaded into sql server 2005 database
which is of xml-type

how to INSERT a particular value in xml file which was loaded into sql server 2005 database
which is of xml-type

update XmlCatalog1 set Document1.modify('delete /X12_U1_837/X12_Q1_837/header/ISA//ISA_Authorization_Information_Qualifier') where id=2

----------
The error which i am getting is
XML Validation: Invalid content. Expected element(s):ISA_Authorization_Information_Qualifier where element 'ISA_Authorization_Information' was specified. Location: /*:X12_U1_837[1]/*:X12_Q1_837[1]/*:header[1]/*:ISA[1]/*:ISA_Authorization_Information[1]

View Replies !   View Related
Insert/Update From ASP.NET And Trigger.
Hi,
 Is it possible to group all my update/insert (one table) ?
Cause I hope to have all the update/insert in one Trigger execution. I need to sum up some figures here.
I am using ASP.NET 2.0, SQLOLEDB and SQL 2005.
 Please Help and Thank you.

View Replies !   View Related
Update/insert Trigger
Hi!I wonder how to use conditions in the inserted table(in ainsert/update) trigger? The inserted table contain all the rows thathave been updated or inserted (for an update/insert trigger), but outof all these rows in inserted table, I only want the rows where aparticular field have been updated, for example if idkey have beenupdated it would be in inserted BUT I only want this row if the fieldamount have been updated. Can a use the UPDATE(column) some how? Anyideas?/Jenny

View Replies !   View Related
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!

View Replies !   View Related
Insert Trigger With An Update
I would like to update a record in a table when a record is added. The dilema is I want to update the record I am adding. I tried an insert trigger ( it doesn't fail, it just doesn't update) If I use the same syntax in TSQL after the record has been inserted it handles the update properly. SO I think the syntax is OK. I have the trigger below. The question is - Is there a way to do this?
Thanks

CREATE TRIGGER transaction_ins_tr
ON dbo.transactions
FOR INSERT
AS
DECLARE @pat_id varchar(5),
@location_name varchar(50),
@account_id char(12),
@tran_num int
--get patient id
SELECT @pat_id = patient_id
FROM inserted
select @account_id = account_id from dbo.patient pa
where pa.patient_id = @pat_id
--get location
select @location_name = df_sitename from
development..patient_ dp
where dp.account_id = @account_id
-- update trans location
update dbo.transactions
set df_sitename = @location_name
where tran_num = @tran_num

View Replies !   View Related
SQL Insert/Update Trigger
I would like to insert an update trigger which should achieve the following functionallity (as per trigger algorithm) but somehow it is not working.  can you please help me since I am still green with SQL and programming, but I need to solve this before I can continue!

 

CREATE TRIGGER ActiveCard

 

ON AccessControl

 

FOR Insert  (when i insert a new row in Access Control)

 

AS UPDATE UniqueCard

( to update the matching (match IDNum) row within UniqueCard table)

 

Set UniqueCard.Active = Inserted.Active        (card and active are of the same type)

Where UniqueCard.IDNum = AccessControl.IDNum

 

Thanks

 

View Replies !   View Related
Insert Works, But Delete And Update Don't In A DetailsView Control
I have 2 Gridviews and a DetailsView for each GridView. The first Gridview and DetailsView work fine and I can Insert, Delete and Update the DetailsView just fine.  However the second Gridview/DetailsView will only let me Insert but not Delete or Update.  When I click on the "Delete" button it just ignores me.  If I do an "Edit", when I try to click on the "Update" button it is ignored again and I have to click on "Cancel".  I don't get any error messages...
 
Anyone have an idea what might be wrong?

View Replies !   View Related
INSERT, UPDATE And DELETE Statements Checkbox Inactive
I have problem in using the SQLDataSource. When in VS 2005 I drag and drop the SQLDataSource onto my page and then add a GridView control.I bind the GridView control to the SQLDataSource control. But the problem is it does not generate the INSERT, UPDATE, and DELETE statements. The dialog box is inactive. The screenshots may help. please help me in this regard. I also tried it for Accesscontrol but the same problem. Sorry for my poor English!. thanks in advance

the screenshot links:
http://img139.imagevenue.com/img.php?image=28285_2_122_937lo.JPGhttp://img205.imagevenue.com/img.php?image=27550_1_122_203lo.JPG 

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved