Create One Trigger For Both Update And Delete

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 !

---

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 !

---

Create Trigger Which Wil Do Event Before Delete A Record..
Hi There, I have created a trigger which supposingly will do event before delete the record from its own table.unfortunately when i try delete the record to test it whether it will do the event (inserting some records to another table), i found that it was not doing the event like i wanted to be. :(the trigger is as below :=======================CREATE TRIGGER TG_D_AGENT ON dbo.AgentFOR DELETEASbegindeclare @vAgentID as numeric,@vAgency as varchar(50),@vUnit as varchar(50),@vAgentCode as varchar(50),@vName as varchar(50),@vIC as varchar(14),@vAddress as varchar(100),@vContactNumber as varchar(50),@vDownlink as varchar(50),@vGSM as varchar(10),@vAM as varchar(10),@vDeleted_date as datetime set @vDeleted_date = convert(datetime, convert(varchar(10) , getdate(),103),103)declare cur_policy_rec CURSOR forselect AgentID,Agency,Unit,AgentCode,[Name],IC,Address,ContactNumber,Downlink,GSM,AM from insertedopen cur_policy_recfetch from cur_policy_rec into @vAgentID,@vAgency,@vUnit,@vAgentCode,@vName,@vIC, @vAddress,@vContactNumber,@vDownlink,@vGSM,@vAM WHILE @@FETCH_STATUS=0BEGIN INSERT INTO [Agent_history] (AgentID,Agency,Unit,AgentCode,Name,IC,Address,Con tactNumber,Downlink,GSM,AM,Deleted_date) VALUES(@vAgentID,@vAgency,@vUnit,@vAgentCode,@vNam e,@vIC,@vAddress,@vContactNumber,@vDownlink,@vGSM, @vAM,@vDeleted_date)fetch from cur_policy_rec into @vAgentID,@vAgency,@vUnit,@vAgentCode,@vName,@vIC, @vAddress,@vContactNumber,@vDownlink,@vGSM,@vAM enddeallocate cur_policy_recend===============================in oracle , i normallly can do something like this...====================================CREATE TRIGGER TG_D_AGENT ON dbo.AgentBEFORE DELETE ON dbo.Agent FOR EACH ROWbeginIs that such thing function like 'BEFORE' in MS SQL SERVER 2000, coz in sql server im not sure they do have or not. Plz someone help me on this...realy appreciated if can!

View Replies !

---

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 !

---

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 !

---

How Can I Create This Update Trigger???
Hi,
 
I have the following situation.
 
I have one table named as "Order", and other table as "Shipment". Now, I want to check if one column name "Status" in the "Örder" table has been changed (when it becomes status = 7) then I need to insert records in "Shipment" table.
 
Order Table:
OID===Order_Product===Order_Description===Order_Status
 
Shipment Table:
SID===Shipment_Description===DateTime===Status
 
I tried to create a trigger, but it is not working properly;
 
Create Trigger trg_InsertShipment ON [dbo].[Order]
FOR Update
AS
INSERT INTO Shipment
(
SID,
Shipment_Description,
DateTime,
Status
)
 
Select
'001' as SID,
'Nothing' as Shipment_Description,
'01/01/2007' as DateTime,
'Ready' as Status
 
WHERE Order.Status = 7
 
===============================================================
 
But it inserts the records into Shipment Table every time the status field or any other field in the Order Table is changed. What I want is that, "When Status Column in Order Table is Updated to 7, then insert record in Shipment Table".
 
How can I do that???
 
Thanks in advance...
 
 

View Replies !

---

Create An Update Trigger
Hi,
I have a table with somefields, here i will only mention on which i need to perform an action, possibly with the use of Trigger.

Fields = Active, inactiveDate
Active Field is of bit datatype mean conatins 1 or 0,
1 means the user is active, but when i change the active field to 0, and make the user inactive i want the date to be populated automatically to inactiveDate field when active field is changed to 0.

any help is much appreciated

NAUMAN

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 !

---

How To Create A Trigger To Update A Field
Hi -
I know my way around VS but I am just exploring "advanced" SQL Server 2005 and have run into a challenge which I think a trigger could solve, but I am not sure and also don't know how to set that up. So any help or links to tutorials are highly appreciated.
Here is the challenge: I have a table with a number of fields, among them RequestID (bigint) and Booktime (datetime). What I would like to happen is whenever someone writes a value different from NULL into RequestID, Booktime gets set to the current timestamp. When RequestID gets set to NULL, Booktime gets set to NULL.
Is there a way to do this with a trigger (or an otherwise elegant way)?
Thanks in advance for ANY help or ideas.
Oliver

View Replies !

---

Create Trigger To Use AFTER DataAdapter.Update()
Hi,

I'm using DataAdapter.Update() to update data in a table. My question is; how do I create a trigger that works after the update has completely finished?

For example if my update adds 50 new rows to a table the trigger I've currently got fires after each new row that is added ie 50 times in total. Is it possible to get it to trigger after the last (ie 50th) row is added???

Thanks

View Replies !

---

Please Help Create Trigger Condition On Update
please help create trigger condition on update

IF EXISTS
(
SELECT *
FROM empList
WHERE (unit = 9)

)
begin

update [dbo].[empList]
set unit = CASE WHEN SELECT na,empID, unit
FROM empList
WHERE (empID IN (111, 222, 333, 555)) AND (unit = 9))
then '4' else t.fld1 end

i have an emmployee table



empid unit

------------------------------------------------------

1111 3

2222 9

3333 9

4444 2

5555 2

6666 1

7777 9

8888 2

9999 9

-----------------------------

i need help create trigger condition on update like this

ONLY ON

EMPID=2222,3333,7777,9999

WHAN ON UPDATE they have unit =9 THAN

I NEED THE trigger DO THIS



empid unit

------------------------------------------------------

1111 3

2222 4

3333 4

4444 2

5555 2

6666 1

7777 4

8888 2

9999 4



ONLY IF someone update EMPID=2222 OR 3333 OR 7777 OR 9999 and UNIT=9

THAN automatic change 9 TO 4



TNX for the help

View Replies !

---

How To Create An Update Or Delete Method In A Strongly Typed Dataset?
Like the subject says, I'm using strongly typed datasets.  I'm using to designer to create the datasets and the methods.  I can select and insert, but I can't update or delete.  I right click on the adapter bar and select Add Query.   I sleect 'Use SQL Statements'I select 'Update' (or 'Delete')I get a sql statement pane containing the word 'Update' ('Delete') and asking 'What data should the table load?'I can click on next, but anything else gives me errors.  I'd list them, but I'm clearly doing something wrong and it's probably obvious. Diane 

View Replies !

---

Create A Trigger To Update A Row That's Been Inserted Or Updated
Hi

 

Apologies if this is a silly question

 

I have a basic table "Customer" which has

Id

Address1

Address2

Address3

Town

County

Postcode

SearchData

 

After I insert or Update a row in this table I need to update the SearchData column

with

 

UPPER(ADDRESS1) + UPPER(ADDRESS2) + UPPER(TOWN) + UPPER(POSTCODE)

 

only for that Id

 

I'm sure this is only a basic update but all the examples I can find are for inserting into other tables not the row itself.

 

Regards

 

David

View Replies !

---

Create User Only With Permissions, To Select, Insert, Update, Delete, And Exec Sps
Hello, I recently view a webcast of sql injection, and at this moment I created a user, and give dbo to this user, and this same user, is the one I have in the connection string of my web application, I want to create a user to prevent sql injection attacks, I mean that user wont be able to drop or create objects, only select views, tables, exec insert,update, deletes and exec stored procedures.

Is any  easy way to do this?

A database role and then assing that role to the user?

View Replies !

---

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

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

declare cur cursor for SELECT currency, country
FROMinserted

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

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



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

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

Thanx!

/Erik

View Replies !

---

How To Create Trigger For Multi Insert Employees Update Tb Employee Once
how to do this
i have table of employee ,evry employee have a unique ID "empid"
empid     VAL_OK
--------------------------
111            0
222            0
333            0
 
now insert multiple insert   to my work_table  shifts  for all month for evry employee
like this
(this is work_table)
empid      date                 val
--------------------------------------------------
111        01/02/2008         1
111        02/02/2008         2
...............
111        29/02/2008         5
--next employee
222        01/02/2008         1
222       02/02/2008          4
...............
222        29/02/2008         6
--next employee
333
--next employee
444
--next employee
555
-------------------------------------------------------------
 

now i need for evry OK insert (for all month)  each employee
go to the TB_Employee
and update each employee once !!
from VAL_OK=0       to  VAL_OK=1
like this
 
empid     VAL_OK
--------------------------
111            1
222            1
333            1
----------------------
like this i know who is the employee have shift for all month and who NOT !
 
i think it like this



Code Snippet
Create trigger for_insert on tb_work
For insert
begin
if @@rowcount = 1
Update tb_employee
Set
val_ok= 1
 
else
/* when @@rowcount is greater than 1,
use a group by clause */
Update tb_employee
set
val_ok= 1
select empid from tb_work
group by tb_work.empid

End
 
 


 

 
TNX

View Replies !

---

How To Create A Trigger Such That It Can Delete The Rows Whenever Any Other Application Such As Biztalk Had Read The Rows ?
I had created a trigger which sees that whether a database is updated if it is its copy the values of the updated row into another control table now I want to read the content of control_table into BIzTalk and after reading I want to delete it.Can any one suggest the suitable ay to do this?

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">&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 !

---

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 !

---

How To Create New CLR Trigger From Existing T-Sql Trigger
how to create new CLR trigger from existing T-Sql Trigger Thanks  in advance

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 !

---