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/
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).
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.
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
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
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 ?
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 ---------------------------
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
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 ---------------------------
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):
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.)
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!
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
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:
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
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 .
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.
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
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
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
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
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
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
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 ?
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
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.
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>
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?
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.
