Tracking Extra Information In A DELETE Trigger
Jan 4, 2008
I'm wondering if there's a good way to sort of pass a value to a trigger, in cases where it needs to store more than just what it can collect from the table it is triggering off of.
We have a database that is used by several applications. Each application connects to the database using a distinct SQL Server Login. So, "Application1" might connect to the database using SQL Server Login "Application1Login".
Each application can be used by any number of users who "log in" to the application. This process merely authenticates them against a stored username and password in the database, but it uses the "Application1Login" for actually connecting to the database. So, when users log in to the application, they're all using the same SQL Server Login to connect to the database. This isn't something that is likely to change, though I realize my question could be answered more simply if each person using the system connected via a specific SQL Server Login.
Whenever an action is performed against the database by an application user, we pass the UserId of the user. We store that UserId in tables when they are inserted or updated. We also have History Tables on several tables, with triggers on Insert, Update, and Delete.
The Insert and Update triggers work great: The stored procedure sets the "LastModifiedByUserId" field in the table to the passed UserId, which causes the history table's entry to use that newly changed information. So, we can always tell who inserted or modified a History table entry by setting the Last Modified By field in the Insert or Update.
The problem is with deletions. Since deletions don't set the LastModifiedByUserId field (it doesn't set fields; it's deleting the row), then the trigger has nothing new to go on. The trigger will cause the History table entry to use the last known value of the LastModifiedByUserId field, which is probably not going to be the person who actually deleted the record.
My solution to this problem is to create a "HistoryDeletedUser" table which only contains a single row, and a single column, which is used to store a UserId value. When performing a deletion, I would first update that table, setting the UserId to the current user's UserId. Then, I delete from the appropriate table. The DELETE trigger would then select the current value from that table, and insert it into the History record as the UserId of the person who deleted the record. After the deletion, I would set the HistoryDeletedUser to Null. As long as I perform my queries within a transaction, it should always be accurate (though I suppose that queries that would otherwise be unrelated to each other would now have to compete for Lock access on the HistoryDeletedUser table...)
View 11 Replies
ADVERTISEMENT
May 24, 2006
Hi There
We have some mystery deletes happening on a table.
The server is very busy, so a profiler trace on this table would result in thousands of events to sift through.
So i would like to put a trigger on this table that on delete could capture information such as the sql statement or user that caused he delete.
Anyone got any ideas on the best way to do this? Or any way to do this ?
Thanx
View 1 Replies
View Related
Sep 29, 2015
Is there any way to track the information about the connections to SSAS cubes through local Excel files (BI usage).
OPreviously, we are tracing the information about the BI usage through the BI SharePoint site. Now we want track the users who are using through local excel files .
View 9 Replies
View Related
Jan 14, 2008
Hi, i am trying to find a trigger that is updating a field in one of my tables.
I have a field called [ProfileDate], whenever a member is profiled, this field is populated with a GETDATE() value. I am trying to track down the trigger that updates this field.
Any help will be greatly appreciated
Kind Regards
Carel Greaves
View 1 Replies
View Related
Jan 28, 2008
Hi,
i hv made a rdl having 2 tables.My requirement is to display the tables on different sheets of excel, so i hv inserted "Page Break At End" in table1.
As a result im getting tables in different sheets but second table (in sheet2) is starting from 2nd row of the sheet i.e. 1st row is coming as hidden, and i want to remove this row as client feed's this excel file in some other application.
If anyone is aware of the solution pl. reply ASAP.
Thank u.
View 3 Replies
View Related
Jan 3, 2008
Hi All SSIS experts,
Happy 2008!!!!
I am inserting data into a tab delimted text file using SSIS package.
After data insetion some extra tabs get added between columns in some rows in the text file.
Can we programmatically delete the extra tabs from the text file, if so how to use/implement the code inside the SSIS package?
Any pointer/suggestions are welcome.
Thanks & Regards,
View 9 Replies
View Related
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
Jul 26, 2001
Hi,
I have a problem here in my SQL Server Database. I want to find which trigger is working backend affecting my table input. Since the original author of this trigger was somebody else, I am not able to find the trigger information
A simple line of syntax would be great.
Thanks in advance
sandeep
View 1 Replies
View Related
Jul 26, 2000
Is there a way to view the triggers on a table using query analyzer?
I have a developer that wants to see this information without having to right click every table in the database and viewing the triggers through enterprise manager.
Thanks,
Brad
View 1 Replies
View Related
Jun 8, 2006
hi guys!Currently i'm facing a bit odd situation. i cant modify my code so ihave to make changes in SQL Server.the problem is, i've modified one table by adding new column and now iwant only those row which has not null value in that column wheneverthe select statement gets fire. i mean in every select statement onlythose rows should be returned which has NOT NULL value in the newlyadded column.for this i've created one view and tried to run the application but thei'm facing some problem as my application is developed in .net 2.0,i've used lots of .net functionalities and now i cant make any changesin Code.another solution i think is triggers. if i can fire a trigger beforeany select statements, my problem can be solved instantly but i'mafraid i can not write andy "before select" trigger.if anyone has any idea or any other solution to my problem, please letme know.all suggestions are welcomed.thanks,Lucky
View 4 Replies
View Related
Nov 29, 2007
Far below (in section "original 3 steps"), you see the following:1. a temp table is created2. some data is inserted into this table3. some of the inserted data is removed based on a join with the sametable that the original select was made fromIn my opinion, there is no way that the join could produce more rowsthan were originally retrieved from viewD. Hence, we could get rid ofthe DELETE step by simply changing the query to be:INSERT INTO #details ( rec_id, orig_corr, bene_corr )SELECT rec_id, 0, 0FROM viewDWHERE SOURCE_SYS NOT IN ( 'G', 'K' )AND MONTH( VALUE_DATE_A8 ) = MONTH( @date )AND YEAR( VALUE_DATE_A8 ) = YEAR( @date )AND INMESS NOT LIKE '2__' ---- the added line===== original 3 steps (mentioned above) =====CREATE TABLE #details (rec_id UNIQUEIDENTIFIER PRIMARY KEY NOT NULL,orig VARCHAR(35) NULL,bene VARCHAR(35) NULL,orig_corr TINYINT NULL,bene_corr TINYINT NULL)INSERT INTO #details ( rec_id, orig_corr, bene_corr )SELECT rec_id, 0, 0FROM viewDWHERE SOURCE_SYS NOT IN ( 'G', 'K' )AND MONTH( VALUE_DATE_A8 ) = MONTH( @date )AND YEAR( VALUE_DATE_A8 ) = YEAR( @date )DELETE dFROM #details dJOIN viewD v ON ( d.rec_id = v.rec_id )WHERE INMESS LIKE '2__'
View 1 Replies
View Related
May 29, 2008
Such as check all triggers that assign value to some columns ?
Thank you very much.
View 1 Replies
View Related
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
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
Nov 5, 2001
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 3 Replies
View Related
Dec 13, 2004
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 3 Replies
View Related
Apr 28, 2008
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 12 Replies
View Related
Feb 27, 2004
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 2 Replies
View Related
Apr 16, 2008
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 1 Replies
View Related
May 13, 2008
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 11 Replies
View Related
Jul 17, 2007
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 1 Replies
View Related
Jul 18, 2007
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 8 Replies
View Related
Aug 15, 2006
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 3 Replies
View Related
Sep 26, 2007
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 2 Replies
View Related
Dec 1, 2004
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 2 Replies
View Related
Feb 15, 2005
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 2 Replies
View Related
Mar 26, 2008
How Can I check that , data in the table which i have to delete is related to the other table,
View 8 Replies
View Related
Feb 18, 2004
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 4 Replies
View Related
May 8, 2008
I have 4 colums in a table
Project, Sections,Tasks,Subtasks
Each subtask will haven a row.
I need to write a trigger when I delete a task it needs to delete all the subtasks relating to it. When I delete a section it needs to delete all the tasks and subasks relating to it. similarly for project.
This trigger for task-subtask works.
CREATE TRIGGER "[Deletetasktrigger]" ON [Tbl] FOR DELETE AS
SET NOCOUNT ON
/* * CASCADE DELETES TO '[Tbl B]' */
DELETE [tbl] FROM deleted, [Tbl] WHERE deleted.[task] = [Tbl].[task]
THis works fine. But when I do it for sections I get this error.
"Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)" ..
Help Please!!!!!!
View 2 Replies
View Related
Oct 31, 2005
i have the following trigger ...anytime the user table is inserted or updated it writes to the UserProfileLog...which is what I want...but I also want it to write into UserProfileLog when the record from Users is deleted
here is my trigger...what am I doing wrong???
CREATE trigger trg_UserProfiles
on dbo.Users
for insert, update, delete
as
insert into UserProfileLog(UserID, UserAccess, UserFirstName, UserLastName, UserName, UserEmail, UserStreet, UserCity, UserState, UserZip, UserHomePhone, UserWorkPhone, UserCellPhone, UserPager, EditedBy)
select UserID, UserAccess, UserFirstName, UserLastName, UserName, UserEmail, UserStreet, UserCity, UserState, UserZip, UserHomePhone, UserWorkPhone, UserCellPhone, UserPager, EditedBy
from inserted
View 2 Replies
View Related
Feb 29, 2008
I've got a ContactInfo table, that stores a variety of information about a contact (i.e. first/last name, address, phone, date of birth, status, etc.). I seem to be randomly loosing contacts though. Older backup's of my database still show a given contact, but the current one doesn't. It's happened a few times, and I can't seem to track what's causing it. (Nothing that I'm doing *should* be causing the contact to be deleted).
I'm looking to create a delete trigger for the table, so that any time a record gets delete, it will record what record got deleted (contactID, firstName, lastName), as well as what time it got deleted, and if possible, what function cause the delete (what function was accessing the table when the delete happened).
I've got some basic knowledge of SQL, and SQL statements, but my knowledge is limited... so any help on this would be greatly appreciated - or, if someone could point me to a website with good suggestions and examples, to help me create a trigger to monitor this stuff. I would be greatly indebted for any help that could be offered.
Here is kind of a shell for a delete trigger that I have been able to put together from some various examples I've found.
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'trDeleteContactInfo' AND type = 'TR')
DROP TRIGGER trDeleteContactInfo
GO
CREATE OR REPLACE TRIGGER trDeleteContactInfo
on ContactInfo
FOR DELETE
AS
EXEC master..xp_sendmail 'my@email.com',
'Contact has just been deleted from ContactInformation Table'
GO
View 1 Replies
View Related
Jul 20, 2005
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 7 Replies
View Related
Jul 20, 2005
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 1 Replies
View Related
