BEGINNER: Simple Delete Trigger
Jul 6, 2006
Hello,
I am trying to learn SQL Server. I need to write a trigger which
deletes positions of the document depending on the movement type.
Here's my code:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE TRIGGER [DeleteDocument]
ON [dbo].[Documents]
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF Documenty.Movement = 'PZ' OR Documents.Movement = 'ZW'
DELETE FROM PositionsPZZW
WHERE Documents.Number IN (SELECT Number FROM deleted);
IF Documents.Movement = 'WZ' OR Documents.Movement = 'RW'
DELETE FROM PositionsWZRW
WHERE Documents.Number IN (SELECT Number FROM deleted);
IF Documents.Ruch = 'MM'
DELETE FROM PositionsMM
WHERE Documents.Number IN (SELECT Number FROM deleted);
END
Unfortunatelly I receive errors which I don't understand:
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 12
The multi-part identifier "Documents.Movement" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 12
The multi-part identifier "Documents.Movement" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 13
The multi-part identifier "Documents.Numer" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 15
The multi-part identifier "Documents.Movement" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 15
The multi-part identifier "Documents.Movement" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 16
The multi-part identifier "Documents.Number" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 18
The multi-part identifier "Documents.Movement" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 19
The multi-part identifier "Dokuments.Number" could not be bound.
Please help to correct the code.
Thank you very much!
/RAM/
View 10 Replies
ADVERTISEMENT
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
Oct 25, 2006
Hi all,
I have been asked to delete a selection of records from a SQL database. I have created a view which SELECTS the records I need to delete. But I have no idea how to convert to DELETE them!
The SQL statement currently is:
SELECT dbo.TransPerPaySequence.*, PaySequence AS PaySeq
FROM dbo.TransPerPaySequence
WHERE (PaySequence IN (138, 125, 115, 110, 98, 90, 84, 76, 69, 63, 54, 46, 36, 25))
Can someone please help with the correct code to convert this to a DELETE action.
Many thanks
View 7 Replies
View Related
Feb 21, 2007
Hiim trying to implement a simple trigger, i had it working fine in oracle but am finding it difficult to convert it to the MS SQL 2005 layout.I have two tables:1) don (columns A,B,C)2) cur (columns A,B)Basically i just want to insert the value of A and B from table don into table cur after an insert.this is what i have:________________________________ALTER TRIGGER [TG_doncur] ON don AFTER INSERTAS BEGININSERT curSET A = A, B=BEND_______________________________i have an if clause aswell but i just wanna get the basics first.any help would be greatBil
View 11 Replies
View Related
Mar 13, 2004
HI all,
i have a table wich contains a datetime field lastedit
i need a trigger that updates that field with the getdate() on the record that a user updated
Thanx
Cheerz Wimmo
View 4 Replies
View Related
Aug 22, 2005
Should i create only one trigger for each table and put all the table functionality in that one trigger?
For example: tr_tblCategory, tr_tblQuestion, tr_tblAnswer
Or should i create a different trigger for each different function on that table?
For example: trReviseDate, trDoThis, trDoThat, trVerifyThis
Is there a better practice in this area?
What do you typically do?
Thanks!
View 5 Replies
View Related
Dec 13, 2005
I am looking at a table in Microsoft SQL Server. I went to thedependencies of this table and it says TRIG_customer. so i amthinking there is a trigger that affects the table but how do i seewhat is the code that resides within this trigger. I looked among thestored procedures but i couldnt find this trigger.are all the triggers listed together somewhere. where is this triggernamed TRIG_customer?thanks in advance
View 1 Replies
View Related
Jan 25, 2008
I do not know if I am in the proper thread, if not thnaks to let me know where to post it..
I have a runing table name CURENTALARM which strore different alarm information.
This table has a Column named STATUS.. When the new inserted rows ocurrs and STATUS =1, then I need to copy that row in a new table name STATUSLOG...
For that I have created a trigger for table CURENTALARM and then do proper commands to insert to other table.
I am using the inserted table in my trigger to fetch last inserted rows.
The question I have is that how to guaranty that each inserted row will fire the triggers properly...
What I mean is that in case I have 2 rows which gets inserted within less than a second in time interval, does the triggers will be able to do its job and proceed properly inserted row or is there a situation that when rows gets inserted too fast, the triger might miss some of them ?
If this is the case how to handl that case
thanks for your reply
regards
serge
View 2 Replies
View Related
Feb 12, 2004
Hi,
I'm getting this error when I try to use "inserted" table in my create trigger call.
---------------------------
Microsoft Development Environment
---------------------------
ADO error: The column prefix 'inserted' does not match with a table name or alias name used in the query.
---------------------------
OK Help
---------------------------
Could you please, help me?
Thanks,
Rovshan
View 2 Replies
View Related
Jan 18, 2006
Hi all, I have a problem with this trigger. It seams to be very simple, but it doesn't work...
I created a trigger on a table and I would want that this one updates a field of a table on a diffrent DB (Intranet). When I test it normally (a real situation), it doesn't work, but when I do an explicit update ("UPDATE AccesCard SET LastMove = getDate();" by example) it works.
If anyone could help me, I would appreciate.
NB: Is there a special way, in a trigger, to update a table when the table to update is on another BD ?
Francois
This is the trigger:
------------------------------------------------------------
ALTER TRIGGER UStatus
ON AccesCard
AFTER UPDATE, INSERT
AS
DECLARE @noPerson int
SET NOCOUNT OFF
IF UPDATE(LastMove)
BEGIN
SELECT @noPerson = Person FROM INSERTED
UPDATE Intranet.dbo._Users SET Intranet.dbo._Users.status = 1 WHERE personNo = @noPerson;
END
SET NOCOUNT ON
View 5 Replies
View Related
Feb 12, 2004
Hi,
I'm getting this error when I try to use "inserted" table in my create trigger call.
---------------------------
Microsoft Development Environment
---------------------------
ADO error: The column prefix 'inserted' does not match with a table name or alias name used in the query.
---------------------------
OK Help
---------------------------
Could you please, help me?
Thanks,
Rovshan
View 2 Replies
View Related
Feb 26, 2008
Experts: Please assist with coding a trigger for a SQL Server 2005 .NET application.
Here's the scenario:
Suppose there are tables MEMBERS, ACTIVITY, and HEADCOUNT that look like this:
MEMBER
member_id (int)
member_name (varchar(50))
...etc
ACTIVITY
activity_id (int)
activity_name (varchar(50))
...etc
HEADCOUNT
headcount_id (int)
member_id (int)
activity_id (int)
...etc
Suppose also that the ACTIVITY table is already populated with several records, say with activity_id = 1, 2, and 3.
OBJECTIVE: When a new member record is added to MEMBER, say member_id 10, insert one record in the HEADCOUNT table for EACH activity in ACTIVITY for that member. Thus, if member #10 is added to MEMBER, then the trigger (or some other mechanism) would add the following records to HEADCOUNT (which, say, already has 30 records):
headcount_id member_id activity_id
31 10 1
32 10 2
33 10 3
I've been advised that a trigger should do the trick for this, but as I'm totally new to SQL, I'll need some help. I'm guessing some iterating SQL command language might be required, but as I'm new to SQL, I don't know how to proceed.
Note that I'm building an ASP.NET application based on VB, and so records will be added to MEMBER through a tableadapter INSERT command. (Though I suspect this has no bearing on trigger behavior.)
Much obliged for your assistance.
-Kurt Euler
San Jose, CA
View 8 Replies
View Related
May 8, 2006
Hi,We have been using ADO and the AddNew method for a long time as a meansto add records to the database. It has always worked fine - no problem.But - we recently started using INSERT triggers that simply call a fewstored procs (they're actually SSNS stored procs that send new eventinfo to notification services). Anyway, these triggers do not seem tofire at all! If I execute an insert command manually from QueryAnalyser, no problems. But the trigger does not fire at all from myapplication!Does anyone know why this could be? For info, my connection string usedby the ADO connection object looks like this: Provider=SQLOLEDB.1;DataSource=XXX;Initial Catalog=YYYAnd my AddRecord ADO code looks like this:With rs.Open sSQL, ConnectionString, adOpenKeyset, adLockOptimistic,adCmdTable And adExecuteNoRecords.AddNewAm I mnissing something obvious here? Any help appreciated!
View 1 Replies
View Related
Jul 20, 2005
I am extremely new at SQL Server2000 and t-sql and I'm looking tocreate a simple trigger. For explanation sake, let's say I have 3columns in one table ... Col_1, Col_2 and Col_3. The data type forCol_1 and Col_2 are bit and Col_3 is char. I want to set a trigger onCol_2 to compare Col_1 to Col_2 when Col_2 is updated and if they'rethe same, set the value on Col_3 to "Completed". Can someone pleasehelp me?Thanks,Justin
View 7 Replies
View Related
Oct 31, 2006
I have just one table but need to create a trigger that takes place after an update on the Orders table. I need it to multiply two columns and populate the 3rd column (total cost) with the result as so:
Orders
---------
ProductPrice ProductQuantity TotalCost
-------------- ------------------ -----------
£2.50 2
£1.75 3
£12.99 2
Can anyone please help me?
View 3 Replies
View Related
Jun 12, 2008
I've this tableCREATE TABLE [dbo].[Attivita]( [IDAttivita] [int] IDENTITY(1,1) NOT NULL, [IDOwner] [int] NULL, [IDAttivitaStato] [varchar](1) COLLATE Latin1_General_CI_AS NULL, [IDAttivitaTipo] [varchar](2) COLLATE Latin1_General_CI_AS NULL, [IDAnagrafica] [int] NULL, [Data] [datetime] NULL CONSTRAINT [DF_Attivita_Data] DEFAULT (getdate()), [Descrizione] [varchar](max) COLLATE Latin1_General_CI_AS NULL, [Privato] [bit] NULL, [LastUpdate] [datetime] NULL CONSTRAINT [DF_Attivita_LastUpdate] DEFAULT (getdate()), CONSTRAINT [PK_Attivita] PRIMARY KEY CLUSTERED ( [IDAttivita] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]How Can I create a trigger for modify the IDAttività Stato field in specific situaion: if IDAttivitaTipo == OK or == NOIDAttivitaStato must be set to CPlease note that I can write in the IDAttivitaStato field (during my code operation).... but I would like also automatic update if the IDAttivitaTipo is OK or NO Can you help me for create this with a trigger?Thanks
View 2 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