Create Trigger Which Wil Do Event Before Delete A Record..

Jun 14, 2007

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.Agent
FOR DELETE
AS
begin
declare @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 for
select AgentID,Agency,Unit,AgentCode,[Name],IC,Address,ContactNumber,Downlink,GSM,AM from inserted

open cur_policy_rec
fetch from cur_policy_rec into @vAgentID,@vAgency,@vUnit,@vAgentCode,@vName,@vIC, @vAddress,@vContactNumber,@vDownlink,@vGSM,@vAM

WHILE @@FETCH_STATUS=0
BEGIN

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

end

deallocate cur_policy_rec
end
===============================

in oracle , i normallly can do something like this...
====================================
CREATE TRIGGER TG_D_AGENT ON dbo.Agent
BEFORE DELETE ON dbo.Agent FOR EACH ROW
begin


Is 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 1 Replies


ADVERTISEMENT

Trigger On Insert To Delete Record

Mar 26, 2008

Can anybody help me in writing a SQl trigger to delete a record when inserted after 70 seconds.?

View 20 Replies View Related

Delete Record Where Values Exist, Else Create Them..

Oct 3, 2000

Dear friends,

I am thinkking there has to be away to do this in a SQL
statement.

can't you do some kind of...

Create Procedure sp_update_users
@User_Id Int,
@Title_Id Int

AS
UPDATE USERS
DELETE WHERE @User_ID = User_Id and @Title_Id = @Title_Id
ELSE
INSERT INTO USERS VALUES (@User_ID,@Title_Id;)

View 3 Replies View Related

Create One Trigger For Both Update And Delete

Apr 30, 2007

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

How Create Trigger Stop Update Delete And Insert

Apr 11, 2008

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

Recovery :: Configure Extended Event To Trigger A Mail Whenever Any Event Occurs

Jun 2, 2015

Recently we migrated our environment to 2012.

We are planning to implement Xevents in all the servers in place of Trace files and everything is working fine.

Is it possible to configure Extended event to trigger a mail whenever any event (example dead lock) occurs.

I have gone through so many websites but i never find.

View 13 Replies View Related

Create Delete Trigger On Table1 To Update A Filed On Table2

Jan 11, 2008

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

How To Create A Trigger Such That It Can Delete The Rows Whenever Any Other Application Such As Biztalk Had Read The Rows ?

Mar 12, 2007

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

Delete Syntax To Delete A Record From One Table If A Matching Value Isn't Found In Another

Nov 17, 2006

I'm trying to clean up a database design and I'm in a situation to where two tables need a FK but since it didn't exist before there are orphaned records.

Tables are:

Brokers and it's PK is BID

The 2nd table is Broker_Rates which also has a BID table.

I'm trying to figure out a t-sql statement that will parse through all the recrods in the Broker_Rates table and delete the record if there isn't a match for the BID record in the brokers table.

I know this isn't correct syntax but should hopefully clear up what I'm asking

DELETE FROM Broker_Rates

WHERE (Broker_Rates.BID <> Broker.BID)

Thanks

View 6 Replies View Related

Delete Record Based On Existence Of Another Record In Same Table?

Jul 20, 2005

Hi All,I have a table in SQL Server 2000 that contains several million memberids. Some of these member ids are duplicated in the table, and eachrecord is tagged with a 1 or a 2 in [recsrc] to indicate where theycame from.I want to remove all member ids records from the table that have arecsrc of 1 where the same member id also exists in the table with arecsrc of 2.So, if the member id has a recsrc of 1, and no other record exists inthe table with the same member id and a recsrc of 2, I want it leftuntouched.So, in a theortetical dataset of member id and recsrc:0001, 10002, 20001, 20003, 10004, 2I am looking to only delete the first record, because it has a recsrcof 1 and there is another record in the table with the same member idand a recsrc of 2.I'd very much appreciate it if someone could help me achieve this!Much warmth,Murray

View 3 Replies View Related

SQL - Cascading Delete, Or Delete Trigger, Maintaining Referential Integrity - PLEASE HELP ME!!!

Nov 13, 2006

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

Datsource Delete Event Fires - Why???

Mar 17, 2008

I have "sqlGetReservationMembers" datasource in my ascx code and another datasource in 'KillReservation'. As you can see "sqlGetReservationMembers" uses a SELECT command only. I have verified this several times. The 3rd block below is the only place in code-behind where "sqlGetReservationMembers" is referenced. When the "KillReservation" fires I get an error stating "Deleting is not supported by data source 'sqlGetReservationMembers' unless DeleteCommand is specified". I'm confused because I can't see where the 'sqlGetReservationMembers' delete event is being fired. I have removed the datasource and recreated it but the problems returns. What am I missing?
Thanks
<asp:SqlDataSource ID="sqlGetReservationMembers" runat="server"                 ConnectionString="<%$ ConnectionStrings:SiteSqlServer %>"                 SelectCommand="sp_GetReservations" SelectCommandType="StoredProcedure">                <SelectParameters>                    <asp:ControlParameter ControlID="gvMyRides" Name="ID"                         PropertyName="SelectedValue" Type="Int32" />                </SelectParameters>            </asp:SqlDataSource>--------------------------------------------------------------------------------- Protected Sub KillReservation(ByVal RideID As Integer, ByVal MemberID As Integer)
 Using Myconnection As New SqlConnection(Config.GetConnectionString("SiteSqlServer"))                Dim Mycommand As New SqlCommand("sp_KillReservation", Myconnection)                Mycommand.CommandType = CommandType.StoredProcedure                Mycommand.Connection.Open()                Mycommand.Parameters.Add(New Parameter("@MemberID", TypeCode.Int32, MemberID))                Mycommand.Parameters.Add(New Parameter("@RideID", TypeCode.Int32, RideID))                Mycommand.ExecuteNonQuery()                Myconnection.Close()        End Using
End Sub----------------------------------------------------------------------------------Protected Sub gvMyRides_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles gvMyRides.SelectedIndexChanged
 sqlGetReservationMembers.SelectParameters.Item("ID").DefaultValue = Me.gvMyRides.SelectedValue Me.gvReservationList.DataSourceID = Me.sqlGetReservationMembers.ID
..More Code..........
End Sub-----------------------------------------------------------------------------------

View 3 Replies View Related

Need Timed Event To Delete Items With NULL Value

May 12, 2007

I am generally a C programmer and have little experience with DB programming, so I apologize right from the start.

I have a table that allows a registration item with a verification item that will be NULL when the item is created. What I wish to do is delete these if the verification item is still NULL after a specified time (say 24 to 48 hours).

I would prefer to do this with some sort of trigger in one of two ways and any suggestions are much appreciated.
1. Have a stored procedure or function (not sure which is best to utilize) that runs every 2 days which checks the table for the NULL values and deletes any older than 48 hours.
2. Create a stored procedure or function when the registration is made that will check if the verification is still NULL for that particular record 24 hours later.
I would think the first would be the simplest, but am not certain.

Again, forgive my inexperience with DB programming and again I appreciate any advice given.

Thanks,
Steven Henley

View 4 Replies View Related

How To Create An Copy Of A Certain Record Except One Specific Column That Must Be Different &&amp; Insert The New Record In The Table

Sep 1, 2006

Hi
I have a table with a user column and other columns. User column id the primary key.

I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key

Thanks.

View 6 Replies View Related

A Trigger That Fire Every Insert Event?

May 21, 2004

This trigger fired once ONLY during an insert event. My web form inserted 4 news records so it should fire the trigger 4 times correct?

CREATE TRIGGER trInsertImplementationTaskP1
on dbo.ImplementationTasks
FOR INSERT
AS
DECLARE @ITIDint

SELECT @ITID = (SELECT ITid
from inserted)

EXEC TrigSendNewIMAlertP1 @ITID

View 4 Replies View Related

Trigger Event After Time Period

Jul 23, 2005

Can anyone offer any advice. I was wondering whether MS SQL server offeredany facilities to trigger an event after a period of time had lapsed? e.g.once a week check data and if it is out of date email an related address.many thanks

View 1 Replies View Related

Help Needed For Identifying DML Trigger Event Type

Jun 19, 2008

Hi,

I have a query regarding DML Triggers.

I have created a trigger ON a table AFTER Insert, Update staement. In the trigger's body I am making a call to a stored procedure. In the procedure, I want to know the Trigger invoked due to which event : insert or Update as I have to handle bith the cases differently.
But I am not able to find out any SQL server function or property which can distinguish between INSERT and Update events of a trigger.

Waiting for the needful.

View 4 Replies View Related

Help Needed For Identifying DML Trigger Event Type

Jun 19, 2008

Hi,

I have a query regarding DML Triggers.

I have created a trigger ON a table AFTER Insert, Update staement. In the trigger's body I am making a call to a stored procedure. In the procedure, I want to know the Trigger invoked due to which event : insert or Update as I have to handle bith the cases differently.
But I am not able to find out any SQL server function or property which can distinguish between INSERT and Update events of a trigger.

Waiting for the needful.

View 2 Replies View Related

Server DDL Trigger Event For Database Restore??

May 4, 2007

Hello. is there an event that exists for server level triggers when a database restore occurs? I don't see restore_database as an option in BOL... I DO see



CREATE_DATABASE

ALTER_DATABASE

DROP_DATABASE



Any way to fire a server trigger on a database restore?



We're capturing the other server events but would like to also log and be notified of database restores. Thanks, Bruce

View 1 Replies View Related

How Can A Trigger Act Asynchronously To The Event That Fired It In Sql Server 2005?

Sep 12, 2007

if service broker is not an option, how can the trigger and sp(s) called by our trigger act asynchronously to the event that fired the trigger in the first place. We are more concerned with the original event being committed than the actions that follow via the trigger.

View 4 Replies View Related

Delete Record From SS7

Jul 31, 2000

I accidently made a duplicate record and I am unable to delete it. Everytime I try to delete, it gives me an error message saying:

Insufficient oe incorrect key coloumn information; too many rows affected by the update.

I am unable to delete either records. Is there another way of accomplishing this task. Thanks for your help in advance.

Bud

View 1 Replies View Related

Cant Delete A Record

Jan 1, 2006

Hello i am new to MS SQL. Just moved in from MS Access.
I used to program in VB6.

mY Question is when i attemp to delete a record the followin error message displays

"Key column information is insufficiant or incorrect.Too many rows were affected by the update."

I have put the as code for the 'DELETE Record' command button in VB6 as follows.

adomain.Recordset.Delete adAffectCurrent

Please help me to safley delete a record from the SQL database using VB6.

View 4 Replies View Related

Delete A Record

Dec 14, 2007

Hi,

How can i delete a record from a table . table contains approx 3 million data and when i execute a query for delete it give error message from transaction log space problem.
Is there any way to stop the log in transaction log and delete the record.


thanks

View 5 Replies View Related

Delete Record

Jul 20, 2005

please helpi was trying to delete a specific record on a table but it still showsthe same record that should be deleted. i dont know how.

View 3 Replies View Related

How To Create New CLR Trigger From Existing T-Sql Trigger

Mar 18, 2008

how to create new CLR trigger from existing T-Sql Trigger Thanks  in advance

View 3 Replies View Related

Best Way To Delete A Parent Record

Jul 22, 2007

whats the best way to delete a parent record? for now what i am doing is to have a special delete page (means not DetailsView/GridView) just to delete parent records so user can see what child records will be deleted too. is the a better way?my code below: The T-SQL Stored ProcedureALTER PROCEDURE dbo.DeleteInventory
(
@InventoryID int
)
AS
DELETE FROM Booking
WHERE InventoryID = @InventoryID

DELETE FROM Inventory
WHERE InventoryID = @InventoryID
  The code in the Code Behind to execute when a button(delete) click  Try
Dim temp As Integer = SqlDataSource4.Delete
If temp > 0 Then
lblDeleteDtatus.ForeColor = Drawing.Color.Blue
lblDeleteDtatus.Text = temp & " Records/Rows Deleted."
Else
lblDeleteDtatus.ForeColor = Drawing.Color.Orange
lblDeleteDtatus.Text = "No records Deleted!"
End If
Catch ex As Exception
lblDeleteDtatus.ForeColor = Drawing.Color.Red
lblDeleteDtatus.Text = "An Error Occured.<br />" & ex.Message.ToString
End Try  any better methods anyone wants to share?

View 2 Replies View Related

Can't Delete Single Record? HELP!

May 28, 2004

OK,

This one is driving me nuts. I've issued a very simple statement to delete a single row from a table. It appears that when I execute it in SQL Query Analyzer the CPUTime spikes and holds one of the CPUs on the box pegged at 100%. I've let this thing run for over a day, and it's not deleting the one damn record. Any thoughts? :confused: :confused: Here's the command I'm executing:

DELETE FROM Invoices WHERE InvoiceID = 153345

Running SELECT * FROM Invoices WHERE InvoiceID = 153345 returns only a single record as it should. InvoiceID is the PK in this table. Any and all help is greatly appreciated. I've rebooted the server, but to no avail. Same thing happens after a reboot.

TIA

View 12 Replies View Related

How To Delete A Record Which Is Referential To Oth

Jun 6, 2008

Hi, I tried to delete a record in tblA which has an ID in tblB, i got error msg saying referential problem, please tell me what should i do? thanks.

View 2 Replies View Related

Delete Duplicate Record

Oct 23, 2006

Hi ,

How can i delete the duplicate record from a table

use Northwind
create table Emp (Ecode char(2), Ename char(10))
Insert into Emp(Ecode, Ename) values('A1','A')
Insert into Emp(Ecode, Ename) values('A1','A')
Insert into Emp(Ecode, Ename) values('A2','B')
Insert into Emp(Ecode, Ename) values('A2','B')
Insert into Emp(Ecode, Ename) values('A3','C')
Insert into Emp(Ecode, Ename) values('A3','C')
Insert into Emp(Ecode, Ename) values('A4','D')
Insert into Emp(Ecode, Ename) values('A4','D')

select * from emp order by Ecode


Thanks

ASM

View 7 Replies View Related

Left Only 1 Record... Delete The Others

Jan 22, 2008

Hi everyone... I have a problem on deleting a record. I accidentally duplicate the record.
I need to delete other records and left only 1 record based on each date and employeenumber.

supposing i have a table hrempshiftschedule
i have 3 fields... empno, shiftdate, and shifttype

sample data...
empno shiftdate shifttype
1 1/1/2008 S1
1 1/1/2008 S1
2 1/1/2008 S2
2 1/1/2008 S2
2 1/1/2008 S2
2 1/2/2008 S2
2 1/2/2008 S2
2 1/3/2008 S3
3 1/1/2008 S3
4 1/4/2008 S4
4 1/5/2008 S5

expected output....

empno shiftdate shifttype
1 1/1/2008 S1
2 1/1/2008 S2
2 1/2/2008 S2
2 1/3/2008 S3
3 1/1/2008 S3
4 1/4/2008 S4
4 1/5/2008 S5

pls help and guide me.... thanks



RON
________________________________________________________________________________________________
"I won't last a day without SQL"

View 6 Replies View Related

Recover Delete Record

Feb 22, 2008

how can i recover delete record from a table. is possible to find who delete record from a table.

View 3 Replies View Related

DELETE Trigger

Jun 14, 2002

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

Delete Trigger

Apr 23, 2001

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







Copyrights 2005-15 www.BigResource.com, All rights reserved