DELETE And UPDATE Trigger Question
Hello
I 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;
END
Actually 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 ?
Thanks
Artashes
View Complete Forum Thread with Replies
Related Forum Messages:
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 !
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 Replies !
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 !
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 !
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 !
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 !
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"> <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> </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 !
SQL - Cascading Delete, Or Delete Trigger, Maintaining Referential Integrity - PLEASE HELP ME!!!
I am having great difficulty with cascading deletes, delete triggers and referential integrity. The database is in First Normal Form. I have some tables that are child tables with two foreign keyes to two different parent tables, for example: Table A / Table B Table C / Table D So if I try to turn on cascading deletes for A/B, A/C, B/D and C/D relationships, I get an error that I cannot have cascading delete because it would create multiple cascade paths. I do understand why this is happening. If I delete a row in Table A, I want it to delete child rows in Table B and table C, and then child rows in table D as well. But if I delete a row in Table C, I want it to delete child rows in Table D, and if I delete a row in Table B, I want it to also delete child rows in Table D. SQL sees this as cyclical, because if I delete a row in table A, both table B and table C would try to delete their child rows in table D. Ok, so I thought, no biggie, I'll just use delete triggers. So I created delete triggers that will delete child rows in table B and table C when deleting a row in table A. Then I created triggers in both Table B and Table C that would delete child rows in Table D. When I try to delete a row in table A, B or C, I get the error "Delete Statement Conflicted with COLUMN REFERENCE". This does not make sense to me, can anyone explain? I have a trigger in place that should be deleting the child rows before it attempts to delete the parent row...isn't that the whole point of delete triggers????? This is an example of my delete trigger: CREATE TRIGGER [DeleteA] ON A FOR DELETE AS Delete from B where MeetingID = ID; Delete from C where MeetingID = ID; And then Table B and C both have delete triggers to delete child rows in table D. But it never gets to that point, none of the triggers execute because the above error happens first. So if I then go into the relationships, and deselect the option for "Enforce relationship for INSERTs and UPDATEs" these triggers all work just fine. Only problem is that now I have no referential integrity and I can simply create unrestrained child rows that do not reference actual foreign keys in the parent table. So the question is, how do I maintain referential integrity and also have the database delete child rows, keeping in mind that the cascading deletes will not work because of the multiple cascade paths (which are certainly required). Hope this makes sense... Thanks, Josh
View Replies !
My &&"On Update&&" Trigger Reacts Also &&"on Delete&&"
In SQL Server 2005 Express I created a trigger which should edit a value in another table when a special tupel column has been updated. The funny thing is, that it also fires on delete...of course not what I wanted. what did I do wrong? Thanks in advance! ALTER TRIGGER dbo.update_history_users ON dbo.[user] AFTER UPDATE AS IF UPDATE([name]) BEGIN DECLARE @old_value nvarchar(MAX), @new_value nvarchar(MAX); SELECT @old_value = name FROM DELETED ; SELECT @new_value = name FROM INSERTED ; UPDATE dbo.history SET user_name = @new_value WHERE user_name = @old_value; END
View Replies !
Trouble With Update Trigger Modifying Table Which Fired Trigger
Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'
View Replies !
Trigger UPDATEing Another Table With An UPDATE Trigger...
Hi, Let's say I have Table A & B. I have a trigger for UPDATE and INSERT on table A that updates a field in table B. This works great. I have just realized that I need to keep the field in table B from being updated by anything other than table A's trigger. Still with me... So I added a trigger to table B for INSERT and UPDATE that looks like the following: IF UPDATE(Quantity) BEGIN RAISERROR(50001, 16, -1) ROLLBACK TRAN END The user can change anything in table B except the Quantity field. Is there a way to disable the trigger on table B from firing when the UPDATE is fired from the trigger on table A? -Alan
View Replies !
Trigger To Update One Record On Update Of All The Tables Of Database
hi! I have a big problem. If anyone can help. I want to retrieve the last update time of database. Whenever any update or delete or insert happend to my database i want to store and retrieve that time. I know one way is that i have to make a table that will store the datetime field and system trigger / trigger that can update this field record whenever any update insert or deletion occur in database. But i don't know exactly how to do the coding for this? Is there any other way to do this? can DBCC help to retrieve this info? Please advise me how to do this. Thanks in advance. Vaibhav
View Replies !
Trigger After Delete - I Would Need A Trigger Before Delete
hello,I googled around some time but I found no solution for this issue(SS2000).I have a table tblB which has to be filled whenever in another tabletblA records are inserted, updated or deleted.So I created one trigger which works fine for inserts and updates andfills my tblB. tblB is filled with other fields which I get from aview vwC.The issue is about this view. When in tblA a record is deleted, thecorresponding record in vwC does not exist and I can't fill tblB. Itried around with INSTEAD OF -Trigger and got error message becausetblA has RI cascades so this is not possible. A temp table could bethe right way? Can you show me an example?thanks--candide_sh
View Replies !
Trigger After Delete - I Need A Trigger Before Delete
hello,I googled around some time but I found no solution for this issue(SS2000).I have a table tblB which has to be filled whenever in table tblArecords are inserted, updated or deleted.So I created one trigger which works fine for inserts and updates andfills my tblB. tblB is filled with other fields which I get from aview vwC. This view vwC is based on a key field used in tblA.The issue is about this view. When in tblA a record is deleted, thecorresponding record in vwC does not exist and I can't fill tblB. Itried around with INSTEAD OF -Trigger and got error message becausetblA has RI cascades so this is not possible. A temp table could bethe right way? Can you show me an example?thanks--candide_sh
View Replies !
Help With Delete Trigger
I have a table, SecurityMasterUnderlying, that has a 2 foreign keyconstraints to the same table (SecurityMaster). The first one is aone-to-one relationship. The second being a many-to-one.* All securities (equities, options, futures) will have ONE row inSecurityMaster.* All derivatives (options) will also have ONE row inSecurityMasterUnderlying.* All derivatives will have an underlying security (think of it as aparent) in SecurityMaster. The underlying security CAN have multiplechilds.I cannot use ON DELETE CASCADE on both FK definitions since thatcreates a circular reference so I need to use a trigger on one ofthem.I am having trouble writing the trigger that will delete thederivative (from SecurityMaster) if the underlying security isdeleted. (With the sample data -- DIAXL should be deleted if DIA is)CREATE TABLE [SecurityMaster] ([Symbol] VARCHAR(15) NOT NULL,[Identity] VARCHAR(15) NOT NULL,[Name] VARCHAR(50) NULL) ON [PRIMARY]CREATE TABLE [SecurityMasterUnderlying] ([Symbol] VARCHAR(15) NOT NULL,[Identity] VARCHAR(15) NOT NULL,[UnderlyingSymbol] VARCHAR(15) NOT NULL,[UnderlyingIdentity] VARCHAR(15) NOT NULL,[Shares] INT NOT NULL) ON [PRIMARY]GOALTER TABLE [dbo].[SecurityMaster]ADDCONSTRAINT [PK_SecurityMaster] PRIMARY KEY NONCLUSTERED ([Symbol], [Identity]) ON [PRIMARY]GOALTER TABLE [dbo].[SecurityMasterUnderlying]ADDCONSTRAINT [FK_SMUnderlying_SM] FOREIGN KEY ([Symbol], [Identity])REFERENCES [dbo].[SecurityMaster] ([Symbol], [Identity]) ON DELETE CASCADEALTER TABLE [dbo].[SecurityMasterUnderlying]ADDCONSTRAINT [FK_SMUnderlying2_SM] FOREIGN KEY ([UnderlyingSymbol], [UnderlyingIdentity])REFERENCES [dbo].[SecurityMaster] ([Symbol], [Identity])GOINSERT INTO SecurityMaster VALUES ('MSFT', '00764G53A', 'Microsoft')INSERT INTO SecurityMaster VALUES ('DIA', '654FE32', 'Diamond')INSERT INTO SecurityMaster VALUES ('DIAXL', '7635TRS', 'DIA Option')INSERT INTO SecurityMasterUnderlying VALUES ('DIAXL', '7635TRS','DIA', '654FE32', 100)
View Replies !
Help On Trigger For Delete
I have a SQL statement that deletes a lot of records in a table (PACCESOS_DET) and a Trigger that fires for delete on the table. The Trigger works fine when only one record is deleted but no when more than record is deleted; it only works for 1 and there is no error message. For each row deleted I need to update a column in another table (PACCESOS_CAB). This the trigger... CREATE TRIGGER ActualizaDiasVisita ON dbo.PACCESOS_DET FOR DELETE AS declare @mdias as int declare @mFKFeria as int declare @mtipo as char(1) declare @mfkcontacto as varchar(7) if exists( select * from PACCESOS_CAB m join deleted i on m.FKFeria= i.FKFeria and m.FKContacto=i.FKContacto and m.Tipo=i.Tipo) begin select @mfkferia=m.fkferia, @mfkcontacto = m.fkcontacto, @mtipo = m.tipo, @mdias = diasvisita from PACCESOS_CAB m join deleted i on m.FKFeria= i.FKFeria and m.FKContacto=i.FKContacto and m.Tipo=i.Tipo update PACCESOS_CAB set diasvisita = @mdias -1 where FKFeria= @mFKFeria and FKContacto=@mFKContacto and Tipo=@mTipo end Thanks in advanced.
View Replies !
Instead Of Delete Trigger
i have created a view from two tables in my database (ProjectedOutputs, and Output) since I am using the view in vb to fill a grid, i am trying to set up a INSTEAD OF DELETE trigger to delete any records in ProjectedOutputs that may be deleted from my grid. Although the records are deleting OK, so are the records in Output, which i dont want. i am not sure how to stop this, or to see if my trigger is actually firing, I would appreiciate any suggestions CREATE TRIGGER Test ON V_ProjectOutputs INSTEAD OF DELETE AS DELETE PROJECTEDOUTPUTS FROM PROJECTEDOUTPUTS JOIN deleted ON PROJECTEDOUTPUTS.ProjectedOutputID = deleted.ProjectedOutputID
View Replies !
Delete Trigger && COM+
Hi, I'm building a website that interacts with MS SQLserver(v7) via a Buisiness Rule-layer developed in COM+ with VB(v6 sp5). The website is an add-on on a existing client/server app. Now I have delete triggers on almost every table and they work perfectly in the c/s app and when runned in the query analyzer. But when I try to do the same with COM+ I get the the next error message on line 10: "Cannot use SAVE TRANSACTION within a distributed transaction." Here follows the code. Hopefully anybody can help me with this problem. SQL-statement: Function getDeleteRequestSQL(ByRef strRequestId As String) As String Dim strSQL As String strSQL = "DELETE FROM thmld2 WHERE right(hdmcode,8)='" & strRequestId & "'" getDeleteRequestSQL = strSQL End Function And then the place where the error occurs. GetConnection cnConn strSQL = getDeleteRequestSQL(reqId) 10 cnConn.Execute strSQL, , adExecuteNoRecords And finaly the trigger: create trigger td_thmld2 on thmld2 for delete as begin declare @numrows int, @errno int, @errmsg varchar(255) select @numrows = @@rowcount if @numrows = 0 return select @numrows = (select count(*) from trigstat with (nolock) ) if @numrows > 0 return save transaction trans_td_thmld2 <-- REASON FOR ERROR? /* delete all children in "thmstat" */ delete thmstat from thmstat t2, deleted t1 where t2.hdmcode = t1.hdmcode /* delete all children in "thmldlk1" */ delete thmldlk1 from thmldlk1 t2, deleted t1 where t2.hdmlmldcode = t1.hdmcode /* errors handling */ error: raiserror @errno @errmsg rollback transaction trans_td_thmld2 end
View Replies !
Delete Trigger
Hello, For SQL Server 6.5. I need help if there is a way to fire a delete trigger when accidentenlty somebody deletes a record from say table AAA then this record should be inserted in another dump table say Table BBBDump with table schema as table AAA plus some other columns like DB_NAME,Time when recorded deleted from table AAA, user ID . Thanks Rajiv
View Replies !
Delete Trigger
hi, I have a table as follow: id amount 1 100 1 100 1 200 1 300 I put a deleted trigger on this table when a record is deleted, I update a balance in another table based on the deleted amount. This works fine when I am deleting one record at a time, but when I delete more than one record, the first value from the first record is captured to update the balance due and the rest of the of the records has not effect. To elaborat more, delete * from table1 where id =1 and amount =200 -- this works fine delete * from table1 where id =1 and amount =100 -- the first 100 only update the balance in another table the second 100 does nothing, any ideas, I appreciate your help. Thanks Ali
View Replies !
DELETE Trigger
Hi, In Oracle, we have FOR EACH ROW Concept in triggers which can be used to manipulate to fire the trigger for each row. How can we implement the same in SQL Server 7.0 ?
View Replies !
Delete Trigger
Hi how can i use delete trigger? For example i've a two table like emp and emp_personal and now what i want to do is i want to delete one row from emp table so how is it possible to delete automatically that emp's details from second table(emp_personal) ? Ex: emp emp_personal emp_id emp_name emp_basic emp_id emp_address 101 Nagu 32,000 101 India 102 Vijay 35,000 102 South Africa 103 Ritesh 30,000 103 U.S I want to delete employee who hav emp_id of 102 from emp table , so how can i delete automatically that employee details from second table i.e. emp_personal ? Is it possible with triggers? Thanx - Nagu
View Replies !
SQL Trigger Delete
Trying to create a trigger on a table that will delete any records in it that do not exist in another table. Inserting into OrderRebateHistory Table. if ordtype, ord_no, and line_seq_no do not exist in oelinhst then delete from OrderRebateHistory or do not insert. Right now my code will insert the record. Then when another record gets inserted the previous record is deleted. CREATE TRIGGER [DeletefromOrderRebateHistory] ON [dbo].[OrderRebateHistory] for INSERT AS DELETE OrderRebateHistory FROM OrderRebateHistory inner join oelinhst_sql on oelinhst_sql.ord_type = OrderRebateHistory.ord_type and oelinhst_sql.ord_no = OrderRebateHistory.ord_no and oelinhst_sql.line_seq_no = OrderRebateHistory.line_seq_no where oelinhst_sql.ord_type <>OrderRebateHistory.ord_type and oelinhst_sql.ord_no <> OrderRebateHistory.ord_no and oelinhst_sql.line_seq_no <> OrderRebateHistory.line_seq_no
View Replies !
DELETE TRIGGER
I'm trying to capture 'what' is deleting records from my tables. I know what the data is but how do I know which user did it or which stored procedure caused it?
View Replies !
Update Trigger Behaviour W/o A Trigger.
Hi, I am not sure if this is the right forum to post this question. I run an update statement like "Update mytable set status='S' " on the SQL 2005 management Studio. When I run "select * from mytable" for a few seconds all status = "S". After a few seconds all status turn to "H". This is a behaviour when you have an update trigger for the table. But I don't see any triggers under this table. What else would cause the database automatically change my update? Could there be any other place I should look for an update trigger on this table? Thanks,
View Replies !
How To Use Update Column For 2 Columns In Update Trigger
I wanted to write a trigger in SQL 2000 checking for an update for 2 columns. If I do one column like "if update(PATIENT_ACPT_STATUS)" it works fine. If I want to add a second column I tried to use "if update(PATIENT_ACPT_STATUS,pat_note)" it doesn't like the syntax. BOL says you can use more than one column but does not give an example. Has anyone tried this yet? Thanks
View Replies !
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 !
Trigger On Delete Statement
Hi! I am deleting some records from the database and at the same time inserting them into a new table. I am doing all this with sql querries. How can that be done with triggers. Basically on the delete, i'd like to insert the affected records. Thanks! James
View Replies !
Why Does The DELETE Trigger Not Work ?
I have the following delete trigger but it doesn't work. ALTER TRIGGER Users_DeleteUsers ON dbo.Users FOR DELETE AS DELETE FROM InstantForum_Members WHERE MemberID IN (SELECT ForumMemberId from Deleted) DELETE FROM InstantKB_Users WHERE UserID IN (SELECT KBMemberId from Deleted) But when I delete a user from Users table, I get an error in this trigger saying no commit or rollback given in trigger. Can someone tell me why this trigger will fail ?
View Replies !
Delete Trigger In SQL Server 7
Have gone through BOL and Google, but can't find the answer... please helpwith a simple Q. I'm trying to create a simple cascade delete trigger in SQLServer 7 where deleting "parent" records in table X delete correspondingchild records in table Y.Table X=========X_IDSOME_VALTable Y=========Y_IDX_IDSOME_VALWhen there is no relationship between X.X_ID and Y.X_ID, the followingtrigger works fine:CREATE TRIGGER "temp" ON xFOR DELETEASdeletefrom ywhere x_id in (select x_id from deleted)However, when a relationship is created to enforce referential integrity,the trigger fails, with a "DELETE statement conflicted with COLUMN REFERENCEconstraint" error. I've seen examples where the trigger says (for example)"AFTER INSERT", where presumably the code is specifically run after theevent that triggers it -- is there a way of forcing the trigger to runbefore the delete from table X is executed? I've tried using "BEFOREDELETE", but no dice :-Thanks!--Aidan Whitehall <aidanwhitehall@fairbanks.co.uk>Macromedia ColdFusion DeveloperFairbanks Environmental Ltd +44 (0)1695 51775
View Replies !
SP Waits For A Delete Trigger?
Hello to all,I have a small question.I call the SP outer the DB. The procedure deletes some record in tableT1.The table T1 has a trigger after delete.This is very importand for me, that the SP will be finished ASAP,that's why, I do not want, and I do not need to wait for a trigger.Does the SP will be finished, after the trigger is finished?Means, does the SP "waits" for a trigger?I think it is like that. Is it anyhow possible, to set the trigger (orthe procedure) that it want's be waiting for a result of triggerexecution?Thank You for kindly replyMateusz
View Replies !
Trigger For Delete Problem
Hi, I have two tables: table A with key name, version table B with key id and foreing key A.name,A.version. The realtion A to B is 1:n I use following trigger to keep data integrity (when record in A deleted, all records with corresponding foreing key should be deleted from table B): CREATE TRIGGER trDelA ON A FOR DELETE AS DECLARE @n int , @ver int SELECT @n = name , @ver = version FROM deleted DELETE B FROM B,A WHERE @n=B.name AND @ver=B.version This trigger works fine, except the case that it doesn't fire when last record deleted from A. why does it happens? how can I fix it? Thank you p.s. i'm using sql server 2000
View Replies !
Help With Delete Trigger Syntax
I have two tables SiteDirections and SiteDirectionsDeleted with the same fields in each table as follows. id, siteid, alpha, advancenotice, keys, accessalerts, directions, approxtraveltime, buildingtype, fwdrequired, roadaccess, createdate, lastupdatedate, lastupdatedby, createdby, parking, dirid, lastupdate, newid, action I want to create a trigger which will move the record which is deleted from SiteDirections over to SiteDirectionsDeleted. I have created the following trigger called "DeleteDirections" as follows. CREATE TRIGGER [DeleteDirections] ON SiteDirections FOR DELETE AS INSERT INTO SiteDirectionsDeleted (id, siteid, alpha, advancenotice, keys, accessalerts, directions, approxtraveltime, buildingtype, fwdrequired, roadaccess, createdate, lastupdatedate, lastupdatedby, createdby, parking, dirid, lastupdate, newid, action) SELECT id, siteid, alpha, advancenotice, keys, accessalerts, directions, approxtraveltime, buildingtype, fwdrequired, roadaccess, createdate, lastupdatedate, lastupdatedby, createdby, parking, dirid, lastupdate, newid, action FROM sitedirections The trigger passes through the syntax check but instead of just moving over the single record that is deleted from SiteDirections over to SiteDirectionsDeleted it ends up moving all the content of SiteDirections into SiteDirections delted. What do I need to change so I only move over the respective deleted record from SiteDirections to SiteDirectionsDeleted? Thanks in advance, equipe9
View Replies !
Conditional Delete Trigger
On the database that I am maintaining we are having some data integrity issues between our Logon table and another sub table that stores the LogonId. The best solution would be to put in a foreign key, but that is going to require a lot of work and a lot of code changes for the entire system. This is what we plan to do, but this is not a quick fix. We need something that can be implemented quickly. The easiest and quickest fix is to check this sub table to see if the LogonId is in the sub table and the row is marked as Active or Working. If it is then we will abort the deletion and raise an error. Otherwise the delete should happen normally. Is aborting the deletion as simple as : <code> Delete From deleted Where LogonId = @myId </code> Or am I missing something? Thanks for the help, Tim
View Replies !
Delete Trigger Print
Just starting this trigger adventure. I was looking for the syntax inside a delete trigger to print the record being deleted. I know the create trigger syntax fine but stuck with the TSQL Here is my 1st effort CREATE TRIGGER rssdelted ON dbo.stm8thd FOR DELETE AS BEGIN print rssd SUSER_NAME(), GETDATE() from deleted end Any help appreciated
View Replies !
DELETE Trigger Problem
I have created a trigger that updates a log table B when a row is deleted from table A. It works OK when I delete a single or multiple rows. However, the problem is that when I try the following DELETE FROM A, I get the following error message: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated." My SQL 7 server has SP3, and none of the columns contain any nulls. Any idea what the problem is? Please help.
View Replies !
Creating A Delete Trigger
I've got a ContactInfo table, that stores a variety of information about a contact (i.e. first/last name, address, phone, date of birth, status, etc.). I seem to be randomly loosing contacts though. Older backup's of my database still show a given contact, but the current one doesn't. It's happened a few times, and I can't seem to track what's causing it. (Nothing that I'm doing *should* be causing the contact to be deleted). Can anyone help me with setting up a simple Delete Trigger, so that whenever a contact gets deleted from the table, it will log as munch information as possible about what just happened (maybe what functions just got ran, what info just changed, whatever...); so that way I can try and get some more information on the problem, and try and diagnose what is causing the deletions. I've got some basic knowledge of SQL, and SQL statements, but my knowledge is limited... so any help on this would be greatly appreciated - or, if someone could point me to a website with good suggestions and examples, to help me create a trigger to monitor this stuff. I would be greatly indebted for any help that could be offered.
View Replies !
Problem With Delete Trigger
I am trying to write a delete trigger that fires when the user deletes the only record for a given entity in the table. In this situation I have an entity PMA which can have one or many uses. I want the trigger to fire if the user tries to delete the last use record for that PMA_NUM from the PMA_USE table. Here is my code: if (SELECT Count(pma_use.pma_num) FROM dbo.PMA_USE inner join dbo.deleted as D1 on dbo.PMA_USE.PMA_NUM= d1.PMA_NUM) = 0 BEGIN RAISERROR ('Cannot delete only Use for PMA!', 16, 1) ROLLBACK TRANSACTION END I seem to be getting inconsistent results when I create the trigger and when I try to test it re the deleted table Sometimes when I run the trigger script in Management Studio Express, it has trouble with my using dbo.deleted and sometimes it doesn't When I try to test the trigger by using MSE to view the table and delete the last use record for a PMA, I get an invalid object cannot find 'dbo.deleted' or cannot find 'deleted' What am I doing wrong? Roberta
View Replies !
Creating A Delete Trigger
I've got a ContactInfo table, that stores a variety of information about a contact (i.e. first/last name, address, phone, date of birth, status, etc.). I seem to be randomly loosing contacts though. Older backup's of my database still show a given contact, but the current one doesn't. It's happened a few times, and I can't seem to track what's causing it. (Nothing that I'm doing *should* be causing the contact to be deleted). I'm looking to create a delete trigger for the table, so that any time a record gets delete, it will record what record got deleted (contactID, firstName, lastName), as well as what time it got deleted, and if possible, what function cause the delete (what function was accessing the table when the delete happened). I've got some basic knowledge of SQL, and SQL statements, but my knowledge is limited... so any help on this would be greatly appreciated - or, if someone could point me to a website with good suggestions and examples, to help me create a trigger to monitor this stuff. I would be greatly indebted for any help that could be offered. Here is kind of a shell for a delete trigger that I have been able to put together from some various examples I've found. IF EXISTS (SELECT name FROM sysobjects WHERE name = 'trDeleteContactInfo' AND type = 'TR') DROP TRIGGER trDeleteContactInfo GO CREATE OR REPLACE TRIGGER trDeleteContactInfo on ContactInfo FOR DELETE AS EXEC master..xp_sendmail 'my@email.com', 'Contact has just been deleted from ContactInformation Table' GO
View Replies !
Trigger On Delete - What Am I Doing Wrong???
i have the following trigger ...anytime the user table is inserted or updated it writes to the UserProfileLog...which is what I want...but I also want it to write into UserProfileLog when the record from Users is deleted here is my trigger...what am I doing wrong??? CREATE trigger trg_UserProfiles on dbo.Users for insert, update, delete as insert into UserProfileLog(UserID, UserAccess, UserFirstName, UserLastName, UserName, UserEmail, UserStreet, UserCity, UserState, UserZip, UserHomePhone, UserWorkPhone, UserCellPhone, UserPager, EditedBy) select UserID, UserAccess, UserFirstName, UserLastName, UserName, UserEmail, UserStreet, UserCity, UserState, UserZip, UserHomePhone, UserWorkPhone, UserCellPhone, UserPager, EditedBy from inserted
View Replies !
Delete Trigger On Same Table
I have 4 colums in a table Project, Sections,Tasks,Subtasks Each subtask will haven a row. I need to write a trigger when I delete a task it needs to delete all the subtasks relating to it. When I delete a section it needs to delete all the tasks and subasks relating to it. similarly for project. This trigger for task-subtask works. CREATE TRIGGER "[Deletetasktrigger]" ON [Tbl] FOR DELETE AS SET NOCOUNT ON /* * CASCADE DELETES TO '[Tbl B]' */ DELETE [tbl] FROM deleted, [Tbl] WHERE deleted.[task] = [Tbl].[task] THis works fine. But when I do it for sections I get this error. "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)" .. Help Please!!!!!!
View Replies !
BEGINNER: Simple Delete Trigger
Hello,I am trying to learn SQL Server. I need to write a trigger whichdeletes positions of the document depending on the movement type.Here's my code:set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoCREATE TRIGGER [DeleteDocument]ON [dbo].[Documents]AFTER DELETEASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;IF Documenty.Movement = 'PZ' OR Documents.Movement = 'ZW'DELETE FROM PositionsPZZWWHERE Documents.Number IN (SELECT Number FROM deleted);IF Documents.Movement = 'WZ' OR Documents.Movement = 'RW'DELETE FROM PositionsWZRWWHERE Documents.Number IN (SELECT Number FROM deleted);IF Documents.Ruch = 'MM'DELETE FROM PositionsMMWHERE Documents.Number IN (SELECT Number FROM deleted);ENDUnfortunatelly I receive errors which I don't understand:Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 12The multi-part identifier "Documents.Movement" could not be bound.Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 12The multi-part identifier "Documents.Movement" could not be bound.Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 13The multi-part identifier "Documents.Numer" could not be bound.Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 15The multi-part identifier "Documents.Movement" could not be bound.Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 15The multi-part identifier "Documents.Movement" could not be bound.Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 16The multi-part identifier "Documents.Number" could not be bound.Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 18The multi-part identifier "Documents.Movement" could not be bound.Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 19The multi-part identifier "Dokuments.Number" could not be bound.Please help to correct the code.Thank you very much!/RAM/
View Replies !
Trigger Before Delete In Firebird Or Mssql
Hi everybody!I need create trigger in firebird and mssql.The trigger must do this:If someone try to delete record in table "mast_trans" and if column "ID"is equal 145 delete cannot be executed otherwise perform delete.How can I do this? My books dont have any examples for this :(Thanks for help, greetings, gregory.
View Replies !
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 !
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 !
|
TRACKER
