MS SQL Server :: Error W/ Trigger When Firing On 'DELETE XYZ WHERE IN (1,2,3)'

Trigger Not Firing On Cascade Delete Table
I have three tables:
BulkMemberHeader - which has a cascade delete on BulkMemberDetail of any related records
BulkMemberDetail €“ which has a DELETE trigger which gets the member ID from deleted and deletes the member record from the member table
Member

This issue:
> When I delete a record from BulkMemberDetail the trigger fires and deletes the record from the Member table as it should
> If I delete a record from the BulkMemberHeader, all corresponding records in BulkMemberDetail are deleted, but the trigger to delete the record in the Member table does not seem to fire

Is it a limitation on SQLServer 2000 that does not allow triggers to fire in a scenario like this?

Any suggestions or comments would be great.

Thanks,

Mike

View Replies !

Trigger Firing And Not Firing
I have a trigger on a table that just updates a last_modified_date and this works fine on our production server. Now I have to update some data and I do not want the trigger to fire. I cannot disable or drop the trigger because the productions systems needs the trigger. Anyone an idea of how to solve this problem?

View Replies !

Delete SP Not Firing?
I cannot get this event to fire. I am using TextBox9 to see if statements are processed and it never fills so it appears the Button4_Click never happens. Any ideas?
Thank you,
<asp:Button ID="Button4" runat="server" OnClick="Button4_Click" Text="Delete Submission" /> protected void Button4_Click(object sender, EventArgs e) { string CompanyDeleteID = TextBox10.Text; SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["localhomeexpoConnectionString2"].ConnectionString); SqlCommand cmd = new SqlCommand("DeleteSubmission", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@C_ID", CompanyDeleteID); TextBox9.Text = "SP completed";
}

PROCEDURE dbo.DeleteSubmission @C_ID intASBEGIN DELETE FROM tblCompanyInfo_Submit WHERE C_ID = @C_ID
DELETE FROM tblStoreStudioSubmit WHERE C_ID = @C_ID
DELETE FROM tblContractorSubmit WHERE C_ID = @C_ID RETURNENDWHERE CD_ID = @C_IDRETURN

View Replies !

Only One Of My SQL Delete Statement Are Firing
Hey guys...
I am trying to tidy up my code a bit and have one SQL command (Sub class) to call when needing to insert, update, delete and select.
I have got one class I am testing with that delete from a table support_ticket and then calls RunSQL() again to delete the corresponding tickets in Support_Reply.
however it only seems to want to delete from one table at a time...as i commented out the first sql and it worked and the second fires...but if the first one is active it doesnt fire.
Do anyone on the forum know why this has happened?

Sub DeleteUserTicket(sender as Object, e as EventArgs)
Dim strSQL1 = "DELETE FROM Support_Ticket WHERE (TicketID = " & txtticketID & ")"
RunSQL(strSQL1)
strSQL1 = "DELETE FROM Support_Reply WHERE (TicketID = " & txtticketID & ")"
RunSQL(strSQL1)
End Sub
'One class to run the sql statements for the entire page this will reduce in repetitve code as well as reduce code size
Sub RunSQL(strSQL)
Dim objCmd As SqlCommand
Dim sqlConn = New SqlConnection(strConn)
objCmd = New SQLCommand(strSQL, sqlConn)
objCmd.Connection.Open()
'Check that the rows can be deleted if not then return a error.
Try

objCmd.ExecuteNonQuery()
response.redirect("ticketsystemtest2.aspx")
Message.InnerHtml = "<b>Ticket " & txtticketID & " Closed</b> <br/>"
Catch ex As SqlException
If ex.Number = 2627 Then
Message.InnerHtml = "ERROR: A record already exists with " _
& "the same primary key"
Else

Message.InnerHtml = "ERROR: Could not update record, please " _
& "ensure the fields are correctly filled out <br>" & ex.Message & " " & ex.Number
Message.Style("color") = "red"
End If
End Try
objCmd.Connection.Close()
sqlConn = nothing
objcmd = nothing
End Sub

cheers

View Replies !

Trigger Not Firing
OK, I'm at a loss..it must be staring me right in the face.

I have a junction table that relates 2 tables, with a unique key of the composit of the 2 keys. There is also an indicator that says 1 relationship between the 2 tables is "primary" and there should only be one of those. So I figured a trigger to take care of it...but I can't seem to get it working...I wrote sample sql to mimic the inserted table as well, and it seems correct, but the trigger just does not fire.

Any ideas?

CREATE TABLE [dbo].[PIF_MEP99] (
[PIFRecID] [int] NOT NULL ,
[MEPRecID] [int] NOT NULL ,
[PrimaryInd] [char] (1) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PIF_MEP99] WITH NOCHECK ADD
CONSTRAINT [PIF_MEP99_PK] PRIMARY KEY CLUSTERED
(
[PIFRecID],
[MEPRecID]
) ON [PRIMARY]
GO

INSERT INTO PIF_MEP99(PIFRecID, MEPRecID, PrimaryInd)
SELECT 1,1,'Y' UNION ALL
SELECT 2,1,'N' UNION ALL
SELECT 3,2,'N'
GO

CREATE TRIGGER dbo_PIF_MEP99_tr_Rule1 ON dbo.PIF_MEP99
FOR UPDATE, DELETE
AS
SET NOCOUNT ON

-- Rule 1: Prevent and MEP from having more than 1 PIF as Primary

IF Exists ( SELECT * FROM inserted i
INNER JOIN PIF_MEP99 p
ON i.MEPRecID = p.MEPRecID
AND i.PrimaryInd = 'Y'
AND p.PrimaryInd = 'Y')
BEGIN
ROLLBACK TRAN
RAISERROR 500003 'Attempting to Insert 2 Primary PIFs for an MEP'
END
GO

SELECT * FROM PIF_MEP99
GO

SELECT * FROM (SELECT 4 AS PIFRecID,1 AS MEPRecID,'Y' AS PrimaryInd) AS i
INNER JOIN PIF_MEP p
ON i.MEPRecID = p.MEPRecID
AND i.PrimaryInd = 'Y'
AND p.PrimaryInd = 'Y'
GO

BEGIN TRAN
INSERT INTO PIF_MEP99 (PIFRecID, MEPRecID, PrimaryInd) SELECT 4,1,'Y'
COMMIT TRAN
GO

SELECT * FROM PIF_MEP99
GO

SELECT * FROM (SELECT 5 AS PIFRecID,1 AS MEPRecID,'Y' AS PrimaryInd) AS i
INNER JOIN PIF_MEP p
ON i.MEPRecID = p.MEPRecID
AND i.PrimaryInd = 'Y'
AND p.PrimaryInd = 'Y'
GO

DROP TABLE PIF_MEP99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

View Replies !

Trigger Not FIRING With BCP
Hi,

Anyone got an idea why this does not work. I have a trigger that is supposed to fire as an INSERT is done on a table. If I manually insert (insert into....), the trigger fires. If I use BCP to insert, the trigger DOES NOT FIRE.....

Davy

View Replies !

Trigger Not Firing ...
Afternoon All ...

I have a trigger on a table that works great when a single record is updated BUT it seems not to fire when I try to do a bulk update as in ...

update TableA

set field3 = ms.field3
FROM TableA as ta,
#ms as ms
where field1 = ms.field1
and field2 = ms.field2

Why does this happen and how can I correct it.

Thanks!

View Replies !

Trigger Not Firing

I have build a SQL Trigger that fires on the update of a specific column; this works perfectly when I test using SQL or even the SQL Server GUI but when I do the update from .NET it doesn€™t fire.

I have a dataset that gets modified and then I call sqlDataAdapter.Update(Dataset) - the row is successfully modified in the database but alas - the trigger isn't fired.

Any help would be appreciated.

View Replies !

Trigger Not Firing On Update
hi,Here's the scenario1) I am running a DTS job to fetch some rows from Oracle2) The job populates the Table A as step 13) Then it fires a update statement which updates the rows in Table B.Here's the statementUPDATE Table B SETtime = case when (select median from Table A where sno = sno and TableA.stno=70 ) is null then timeelse (select median from Table A where Table B.sno = Table A.sno andTable A.sstno=70) end ,endWHERE EXISTS (select sstno from Table A where Table B.sno = TableA.sno)There is a trigger on table B which should fire as soon as thevalue>15.When I fire the update statement direcly with a higher value than itfires the trigger.update table B set time=17 where b.sno=1000But not when the job runs...I am puzzled.Thoughts?AJ

View Replies !

Trigger Not Firing, DTS Load
I have a table that is getting refreshed from DB2 using DTS (I believe the DBA is doing a DELETE and an APPEND). I have a trigger on this table ON APPEND, INSERT, but the trigger never fires. When I manually update the data, the trigger fires no problem...

Is DTS capable of updating a SQL Server table without firing the trigger?

I'm an Oracle guy, and this is my 1st experiences with SQL Server, so I'll put the code here and if you want to point out any bad practices (such as the way i converted the DB2 TIMESTAMP to a SQL Server DATETIME , please do.

FYI, the DB2 TIMESTAMP is getting loaded into the SQL Server table as a VARCHAR(26)
Carl

CREATE TRIGGER trig_SAWakeUp ON tsnro
FOR INSERT, UPDATE
AS
DECLARE @snro_stus char(10)
DECLARE @snp_sht_dtm as datetime
SELECT @snro_stus = snro_stus
FROM tsnro
IF (RTRIM(@snro_stus) = 'ASSIGNED') OR (RTRIM(@snro_stus) = 'REFRESHED')
BEGIN
SELECT @snp_sht_dtm = CONVERT(DATETIME, SUBSTRING(evt_dtm,1,19))
FROM tsnro

INSERT INTO TSNP_SHT_DTM (SNRO_STUS, SNP_SHT_DTM )
VALUES (@snro_stus, @snp_sht_dtm)
END
ELSE
BEGIN
INSERT INTO TSNP_SHT_DTM ( SNRO_STUS, SNP_SHT_DTM )
VALUES (@snro_stus, @snp_sht_dtm)
END

View Replies !

Insert Trigger Sometimes Not Firing
hi all

i have an issue with an insert trigger sometimes not firing.

here is the trigger

CREATE TRIGGER Insert_tPABillToAddr ON [dbo].[tPA00175]
FOR INSERT
AS

INSERT into tPABillToAddr
(
chrJobNumber
)

SELECT chrJobNumber
FROM inserted

when the user enters a new this table is to insert one column into another table. the thing is, sometimes it does not do the insert. any ideas as to why? it is a very uncommon thing, lets say once out of every 20 inserts does it fail. but it is crucial that it never fails.

thanks

View Replies !

Prevent Trigger From Firing
Hi,

I have an update tigger on one of my tables.
I want to fire an Update SQL but somehow prevent trigger from firing.

Any Ideas..

View Replies !

Artificially Firing A TRIGGER
I have a table (table 1) that has a trigger attached to it, which sends data to table 2. Both tables are attached to an appliction.

In theory, the trigger populates table 2 (from table 1) from the data entered from the user interface application.

But when I import data from the backend

via DTS into Table 1, the trigger does not send the data to table 2.

What do I need to do ???

Thank you in advance !!

View Replies !

Update Trigger Not Firing.

Hi All,

I have an update trigger which stores the date a record was updated by a GIS app.

It worked in SQL2000 but when I copied and pasted the code into SQL2 its not firing. Any Ideas? Below is the code.

Create TRIGGER [dbo].[Date_Entered] ON [dbo].[ZONING_OP]

FOR UPDATE

AS

UPDATE [dbo].[ZONING_OP]

Set Date_Entered = GetDate()

Where ID = (Select ID from Inserted)

View Replies !

AFTER INSERT Trigger Not Firing In SQL 2005
Nothing fancy; just a trigger on a sharepoint table that supposed towrite a record to another SQL table.set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER TRIGGER [TV_UpdateFileSyncProgress]ON [dbo].[Docs]AFTER INSERTASBEGINSET NOCOUNT ON;BEGINIF EXISTS (SELECT null FROM inserted WHERE DirName like'csm/%/Shared Documents')BEGINIF NOT EXISTS (SELECT null FROM inserted INNER JOINTV_FileSyncProgress fp ON LOWER(RTRIM(fp.LeafName)) =LOWER(RTRIM(Replace(Replace(inserted.DirName,'csm/',''),'/SharedDocuments','') + '' + inserted.LeafName)))BEGININSERT INTO TV_FileSyncProgress (InternalOrigin, ExternalOrigin,ChangeType, SiteId, DirName, LeafName, FlagForDelete)SELECT0,1,1,SiteId,'F:commonExtranet',Replace(Replace (DirName,'csm/',''),'/SharedDocuments','') + '' + LeafName,0 FROM insertedENDENDENDEND

View Replies !

Create Trigger Not Firing Via ODBC
I created a trigger in the "source table" that will "feed" and secondtable. The trigger is as follows:CREATE TRIGGER [FeedToP21] ON dbo.FromUPSFOR INSERTASDeclare @Count intSelect @Count = Count(*) from InsertedIf @Count > 0BeginInsert into ToP21Select i.* From Inserted iLeft Join ToP21 ton i.recnum = t.recnumWhere t.recnum is nullEndIf @@ERROR != 0Rollback TranA record was created in the "source table" via ODBC, however, thetrigger does not seem to have fired to create the record in the secondtable.If I create a record manually using SQL Server Enterprise Managerwithin the "tableview" the trigger fires and a duplicate record iscreated in the second table.Is there a fix for this problem?Thank you in advance.

View Replies !

UPDATE Trigger Firing Too Late?
Hi. This SHOULD be something simple, but I am apparently missing something. I have a Users table where a user's status is a varchar(100). I'm trying to implement a trigger so that when a user's status is changed to any string other than what it was before the update the trigger would change the LastUpdated field to current date/time.

Here's the trigger, I replaced the update of LastUpdated with a simple print statement. For some reason, it seems like the trigger is firing after the update statement has committed because the values of @m_status_new and @m_status_old are always the same so this trigger always prints 'status has not changed.' What am I doing wrong? Thank you greatly for any help provided.

USE myDB
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'tr_update_users' AND type = 'TR')
DROP TRIGGER tr_update_users
GO

CREATE TRIGGER tr_update_users
on dbo.Users
FOR UPDATE
AS

DECLARE @m_status_new varchar(100),
@m_status_old varchar(100),
@m_UserID int

SELECT @m_status_new = UserStatus,
@m_UserID = UserID
FROMinserted

SELECT @m_status_old = UserStatus
FROMUsers
WHEREUserID = @m_UserID

IF @m_status_new != @m_status_old
BEGIN
print 'status has changed'
END
ELSE
BEGIN
print 'status has not changed'
END
GO

View Replies !

UPDATE TRIGGER WITHOUT RECURSIVE FIRING
I have an update trigger I created that updates a field based on the user who last updated the record. Under 7 the only way it would work was to have recursive triggers firing turned on. Under 2000 might there be a btter solution. The code is below. Thanks

CREATE trigger tr_cmsUpdt_MARS on dbo.PATIENT_MEDICATION_DISPERSAL_ for UPDATE as
-- updates record with sql user and timestamp
--created 11-28-00 tim cronin
DECLARE @muser varchar(35),
@rec_lock_status int,
@ptacpt_status int
set @muser = current_user
begin
UPDATE PATIENT_MEDICATION_DISPERSAL_
set MODIFIED_BY = @muser,
MODIFIED_TS = getdate()
from deleted dt
WHERE --DT.MODIFIED_BY <> 'DBO' AND
PATIENT_MEDICATION_DISPERSAL_.RECORD_ID = dt.RECORD_ID
end

View Replies !

Trigger Firing Multiple Stored Procedure
DESCRIPTION: I have an FTP server set up to log via ODBC into a table FTPLog. The trigger on table FTPLog fires when new files are received to process and load the file via a stored procedure.

CREATE TRIGGER tr_new_file ON FTPLog
AFTER INSERT
AS
SET NOCOUNT ON
DECLARE @filename varchar(50), @logtime datetime
DECLARE c1 CURSOR
FOR SELECT filename, logtime
FROM inserted
OPEN c1
FETCH NEXT FROM c1
INTO @filename, @logtime
WHILE @@fetch_status = 0
BEGIN
EXEC sp1 @filename, @logtime
FETCH NEXT FROM c_inserted
INTO @filename, @logtime
END
CLOSE c1
DEALLOCATE c1
END

PROBLEM: There are multiple problems with this setup. The first problem is that when the stored procedure gets executed it takes a long time to process the file and the FTP server never returned a completion code to the ftp client and ended with a connection time out from the client. My users keep asking if the FTP failed but it didnt fail. The server returned a completion code too late.

PROBLEM2: When multiple files are ftp to the server on the same session, only the first one gets process. Even though my code loops through all the records because the processing takes a long time the second one never gets executed. If I replace the EXEC sp1 statement with a PRINT statement then it's working fine.

SOLUTIONS and SUGGESTIONS highly appreciated.

View Replies !

Importing Data In Datatable Using SSIS Package Trigger On Insert Is Not Firing On That Table
Hi
I am Importing data in datatable using SSIS package . I made trigger on that table on insert. The trigger on insert is not firing on that table
Please help
Thanks
CP

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 !

Firing A Trigger When A User Updates Data But Not When A Stored Procedure Updates Data
I have a project that consists of a SQL db with an Access front end as the user interface. Here is the structure of the table on which this question is based:

Code Block

create table #IncomeAndExpenseData (
recordID nvarchar(5)NOT NULL,
itemID int NOT NULL,
itemvalue decimal(18, 2) NULL,
monthitemvalue decimal(18, 2) NULL
)
The itemvalue field is where the user enters his/her numbers via Access. There is an IncomeAndExpenseCodes table as well which holds item information, including the itemID and entry unit of measure. Some itemIDs have an entry unit of measure of $/mo, while others are entered in terms of $/yr, others in %/yr.

For itemvalues of itemIDs with entry units of measure that are not $/mo a stored procedure performs calculations which converts them into numbers that has a unit of measure of $/mo and updates IncomeAndExpenseData putting these numbers in the monthitemvalue field. This stored procedure is written to only calculate values for monthitemvalue fields which are null in order to avoid recalculating every single row in the table.

If the user edits the itemvalue field there is a trigger on IncomeAndExpenseData which sets the monthitemvalue to null so the stored procedure recalculates the monthitemvalue for the changed rows. However, it appears this trigger is also setting monthitemvalue to null after the stored procedure updates the IncomeAndExpenseData table with the recalculated monthitemvalues, thus wiping out the answers.

How do I write a trigger that sets the monthitemvalue to null only when the user edits the itemvalue field, not when the stored procedure puts the recalculated monthitemvalue into the IncomeAndExpenseData table?

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 !

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 !