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?
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.
Thanks for looking at this post. I currently have a trigger that fires when a row is inserted or deleted on a table. The idea behind the trigger is that when a row is inserted (representing a sub-category for images), the categories parent needs to have some work done on it. I currently have the trigger working just fine with single inserts and single deletes:
sql Code:
Original - sql Code
CREATE TRIGGER smvcModImageManagerCategory_insert_delete_pdfManger_sync ON smvcModImageManagerCategory FOR INSERT, DELETE AS DECLARE @pdfId INTEGER; DECLARE @parentPdfId INTEGER; DECLARE @grandparentPdfId INTEGER; DECLARE @parentId INTEGER; DECLARE @grandparentId INTEGER; DECLARE @tableName VARCHAR( 255 );
-- If I am being inserted or deleted, and I am not a top level -- category, then my parent's pdf record needs to be set so that -- the pdf file is updated IF (SELECT id FROM Inserted) IS NOT NULL BEGIN SELECT @pdfId = (SELECT pdfManagerId FROM Inserted), @parentId = (SELECT parentCategory FROM Inserted), @grandparentId = (SELECT parentCategory FROM smvcModImageManagerCategory WHERE id = @parentId); END ELSE BEGIN SELECT @pdfId = (SELECT pdfManagerId FROM Deleted), @parentId = (SELECT parentCategory FROM Deleted), @grandparentId = (SELECT parentCategory FROM smvcModImageManagerCategory WHERE id = @parentId) END
-- If I am not a top level category, set my parent's pdf to be -- updated IF @parentId <> -1 BEGIN SELECT @parentPdfId = (SELECT pdfManagerId FROM smvcModImageManagerCategory WHERE id = @parentId); UPDATE smvcModPdfManager SET data_last_updated = GETDATE() WHERE id = @parentPdfId; END GO
CREATE TRIGGER smvcModImageManagerCategory_insert_delete_pdfManger_syncON smvcModImageManagerCategoryFOR INSERT, DELETEAS DECLARE @pdfId INTEGER; DECLARE @parentPdfId INTEGER; DECLARE @grandparentPdfId INTEGER; DECLARE @parentId INTEGER; DECLARE @grandparentId INTEGER; DECLARE @tableName VARCHAR( 255 ); -- If I am being inserted or deleted, and I am not a top level -- category, then my parent's pdf record needs to be set so that -- the pdf file is updated IF (SELECT id FROM Inserted) IS NOT NULL BEGIN SELECT @pdfId = (SELECT pdfManagerId FROM Inserted), @parentId = (SELECT parentCategory FROM Inserted), @grandparentId = (SELECT parentCategory FROM smvcModImageManagerCategory WHERE id = @parentId); END ELSE BEGIN SELECT @pdfId = (SELECT pdfManagerId FROM Deleted), @parentId = (SELECT parentCategory FROM Deleted), @grandparentId = (SELECT parentCategory FROM smvcModImageManagerCategory WHERE id = @parentId) END -- If I am not a top level category, set my parent's pdf to be -- updated IF @parentId <> -1 BEGIN SELECT @parentPdfId = (SELECT pdfManagerId FROM smvcModImageManagerCategory WHERE id = @parentId); UPDATE smvcModPdfManager SET data_last_updated = GETDATE() WHERE id = @parentPdfId; ENDGO
However, when I execute a statement like:
sql Code:
Original - sql Code
DELETE FROM smvcModImageManagerCategory WHERE (smvcModImageManagerCategory.id IN ('86','87','88','90','91'))
DELETE FROM smvcModImageManagerCategory WHERE (smvcModImageManagerCategory.id IN ('86','87','88','90','91'))
I get an error because the virtual 'Deleted' table has more than one record in it. So, what I really need is advice on how to turn the above trigger into something that will be able to handle multiple deletes.
I'm trying to create relational database with some triggers in SQL Server 7.0, but it doesn't work as expected. Let's say that I have 'Office' database with two tables, 'Users' and 'UserRights' (userRights table should have much more rights, but that's not relevant for this problem):
CREATE TABLE [Users] ( [FS_Username] [nvarchar] (8) NOT NULL , [FS_Password] [nvarchar] (32) NOT NULL , CONSTRAINT [PK_Users] PRIMARY KEY NONCLUSTERED ( [FS_Username] ) ON [PRIMARY] ) ON [PRIMARY] GO
CREATE TABLE [UserRights] ( [FS_Username] [nvarchar] (8) NOT NULL , [FI_UserType] [int] NOT NULL CONSTRAINT [DF_UserRights_FI_UserType] DEFAULT (1), [FI_AllowLogin] [int] NOT NULL CONSTRAINT [DF_UserRights_FI_AllowLogin] DEFAULT (1), CONSTRAINT [PK_UserRights] PRIMARY KEY NONCLUSTERED ( [FS_Username] ) ON [PRIMARY] , CONSTRAINT [FK_UserRights_Users] FOREIGN KEY ( [FS_Username] ) REFERENCES [Users] ( [FS_Username] ) ) ON [PRIMARY] GO
Foreign Key CONSTRAIN above is created by adding both tables to the diagram and defining relationship between these two tables FS_Username field, where 'Enable relationship for INSERT and UPDATE' option is turned ON. You can easily see this if you create diagram youself and insert these two tables in it. Next to this, I created two triggers that should handle inserting/deleting rows in UserRights table as consequence of inserting/deleting rows in Users table:
CREATE TRIGGER InsertUserRights ON Users FOR INSERT AS BEGIN INSERT INTO UserRights (FS_Username) (SELECT FS_Username FROM Inserted) END
CREATE TRIGGER DeleteUserRights ON Users FOR DELETE AS BEGIN DELETE UserRights WHERE FS_Username IN (SELECT FS_Username FROM Users) END
Now, when (manually) I insert row in Users table, UserRights table gets updated accordingly. HOWEVER, when I try to delete one or more entries from Users table, I get error report. For example, if you try to execute following two commands:
Insert Into Users (FS_Username, FS_Password) VALUES ('John', 's')
Delete from Users
... first command will succede, but second one will fail with message:
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_UserRights_Users'. The conflict occurred in database 'Office', table 'UserRights', column 'FS_Username'. The statement has been terminated.
Does anyone know how to resolve this problem without loosing constrains and triggers ? (If I turn off 'Enable relationship for INSERT and UPDATE' option for relationship, things will work fine, but than I can make inconsistent data in UserRights table).
Where Trace_EnterId is the parent ID and BubbledTraceEnter_Id is the child ID. My test case has nested trace lines that go down 5 levels, but when I delete the top trace line, only the child directly under it is deleted leaving the other 3 as orphans. Here's the trigger:
ALTER TRIGGER [dbo].[DTrig_xTrace] ON [dbo].[ix_Trace] FOR Delete AS BEGIN SET NOCOUNT ON; DELETE FROM ix_Trace FROM ix_Trace INNER JOIN deleted as dt ON ix_Trace.Trace_BubbledTraceEnter_Id = dt.Trace_EnterId
Background: Am working on completing an ORM that can not only handles CRUD actions -- but that can also updates the structure of a table transparently when the class defs change. Reason for this is that I can't get the SQL scripts that would work for updating a software on SqlServer to be portable to other DBMS systems. Doing it by code, rather than SQL batch has a chance of making cross-platform, updateable, software...
Anyway, because it needs to be cross-DBMS capable, the constraints are that the system used must work for the lowest common denominator....ie, a 'recipe' of steps that will work on all DBMS's.
The Problem: There might be simpler ways to do this with SqlServer (all ears :-) - just in case I can't make it cross platform right now) but, with simplistic DBMS's (SqlLite, etc) there is no way to ALTER table once formed: one has to COPY the Table to a new TMP name, adding a Column in the process, then delete the original, then rename the TMP to the original name.
This appears possible in SqlServer too --...as long as there are no CASCADE operations. Truncate table doesn't seem to be the solution, nor drop, as they all seem to trigger a Cascade delete in the Foreign Table.
So -- please correct me if I am wrong here -- it appears that the operations would be along the lines of: a) Remove the Foreign Key references b) Copy the table structure, and make a new temp table, adding the column c) Copy the data over d) Add the FK relations, that used to be in the first table, to the new table e) Delete the original f) Done?
The questions are: a) How does one alter a table to REMOVE the Foreign Key References part, if it has no 'name'. b) Anyone know of a good clean way to get, and save these constraints to reapply them to the new table. Hopefully with some cross platform ADO.NET solution? GetSchema etc appears to me to be very dbms dependant? c) ANY and all tips on things I might run into later that I have not mentioned, are also greatly appreciated.
hello, once upon a time when i created my db (originally in access then used the conversion tool, which i now know is wrong!) i thought it would be an amazing idea to have cascading updates and deletes, however it turns out now this is exactly not what i want! if i leave them in then it throws errors when i delete records out of my stock table as related records are in the order_line table here is the code (well i think so, im not the best at sqlserver as you probably can tell already) that im using if anyone can help or point me in the right direction that would be great, thanks USE [nashdfDB1]GO/****** Object: Table [dbo].[tbl_stock] Script Date: 07/13/2007 02:52:14 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[tbl_stock]( [Stock_ID] [int] IDENTITY(1,1) NOT NULL, [cat_id] [int] NOT NULL CONSTRAINT [DF__tbl_stock__cat_i__15502E78] DEFAULT ((0)), [sub_cat_id] [int] NULL CONSTRAINT [DF__tbl_stock__sub_c__164452B1] DEFAULT ((0)), [location] [int] NULL CONSTRAINT [DF__tbl_stock__locat__173876EA] DEFAULT ((0)), [n_or_sh] [varchar](50) NULL, [title] [varchar](255) NULL, [description] [varchar](255) NULL, [size] [varchar](50) NULL, [colour] [varchar](50) NULL, [cost_price] [decimal](9, 2) NULL CONSTRAINT [DF__tbl_stock__cost___182C9B23] DEFAULT ((0)), [selling_price] [decimal](9, 2) NULL CONSTRAINT [DF__tbl_stock__selli__1920BF5C] DEFAULT ((0)), [qty] [varchar](50) NULL, [date] [datetime] NULL CONSTRAINT [DF__tbl_stock__date__1A14E395] DEFAULT (getdate()), [condition] [varchar](255) NULL, [notes] [varchar](255) NULL, [visible] [bit] NULL CONSTRAINT [DF__tbl_stock__visib__1B0907CE] DEFAULT ((1)), [picture1] [varchar](50) NULL, [picture1_thumb] [varchar](50) NULL, [picture2] [varchar](50) NULL, [picture2_thumb] [varchar](50) NULL, [picture3] [varchar](50) NULL, [picture3_thumb] [varchar](50) NULL, [picture4] [varchar](50) NULL, [picture4_thumb] [varchar](50) NULL, [display_price] [varchar](50) NULL, [created_by] [varchar](50) NULL, [buying_in_recipt] [varchar](255) NULL, CONSTRAINT [tbl_stock$PrimaryKey] PRIMARY KEY CLUSTERED ( [Stock_ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] GO SET ANSI_PADDING OFF
I have contract table which has built in foreign key constrains. How can I alter this table for delete/ update cascade without recreating the table so whenever studentId/ contactId is modified, the change is effected to the contract table.
Thanks
************************************************** ****** Contract table DDL is
create table contract( contractNum int identity(1,1) primary key, contractDate smalldatetime not null, tuition money not null, studentId char(4) not null foreign key references student (studentId), contactId int not null foreign key references contact (contactId) );
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
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?
I have a table where table row gets updated multiple times(each column will be filled) based on telephone call in data.  Initially, I have implemented after insert trigger on ROW level thinking that the whole row is inserted into table will all column values at a time. But the issue is all columns are values are not filled at once, but observed that while telephone call in data, there are multiple updates to the row (i.e multiple updates in the sense - column data in row is updated step by step),
I thought to implement after update trigger , but when it comes to the performance will be decreased for each and every hit while row update.
I need to implement after update trigger that should be fired on column level instead of Row level to improve the performance?
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
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
I have a Sql Server 2005 table with 3 fields, an ID field (primary key), a parent ID field, and Name. The parent ID references the ID field (foreign to primary - many to one) within the same table so that records can reference their parent. I would like to place a cascade delete on the ID field so that when the primary ID is removed it will automatically remove all those records with a parent ID that match. Sql server does not allow me to establish this cascade delete.I was considering a trigger instead but only know how tio use the AFTER paramter and not an alternative. Thanks
I am a C# programmer also acting as the dba for a SQL Server project. I have a table called Folder that has child records in a table called FolderItems. When a Folder is deleted I want the FolderItems to be deleted. I know how to accomplish via code, but I would prefer to have SQL Server perform the delete just in case a folder is deleted via SQL Management Studio or other method.
Is a trigger the way to go about this? I know how to create triggers For Delete, but I just wondering is this the best method to create a Cascade delete or does SQL have any built in "Cascade" delete features.
Helle people. The question is: I have two tables: Directories and SubDirectories. SubDirectories table has foreign key for DirectoryId. Now if I define this key with ON DELETE CASCADE attribute then in case of I delete a Directory record, all SubDirectory records will also be deleted. But if I have only one table Directories that have field named ParentId and in this field I save an Id of parent direcory. Can I define a parentId field as a foreign key to the same table and define it with ON DELETE CASCADE? Will it work too? Thanks
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.....
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
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.
Hi, I have the following tables: Categories {Category_ID, Column2, ...} Articles { Article_ID, Category_FK, Column3, ...} Discussions {Discussion_ID, Article_FK, Column3, ...} Now, all what I have is just category_ID value (Let us say 3), how can I do cascade delete to delete category's record that its ID = 3 and delete all articles and all discussions that found in that category?
Is it possible to cascade update and delete while DRI remains there ? That is without deleteing refrential integrity. If anyone have example of cascade delete and Update using Pubs or Northwind Example , i'll be really obliged.
I am attempting to clarify how the cascade delete works. For example I have a Parent table and two child tables used for lookup that have the ID_FK = ID_PK from the Parent table.
tblUsers --------------- ID_PK Fname Lname ...
tblUser_Phone ---------------- ID_FK Phone ...
tbUser_Email ---------------- ID_FK Email ...
If I needed to delete all records from tblUser_Phone would this delete my USER entry from the parent table tblUsers if I have cascade delete enabled?
Hi all, I've been reading for a couple of weeks but this is my first question so please be gentle with me.
I'm pretty new to SQL Server, though I've worked with Access for years. I've got four tables (I'm simplifying) and I need to set up cascade deletes between them. I understand how to create foreign keys and all that, but SQL Server is telling me it can't create my keys because it will create multiple cascade paths. I understand that too, I just need to find a way around it. Here are the tables...
dbo.JOBS Job_Number
dbo.COLUMNS JOBS_Job_Number Column_Number
dbo.ROWS JOBS_Job_Number Row_Number
dbo.GRID_DATA JOBS_Job_Number Column_Number Row_Number Data
Currently I have keys set to cascade delete between JOBS>COLUMNS and JOBS>ROWS so that when the user deletes a job, the columns and rows for that job are deleted as well. No problem there.
If the user deletes a column, I have to delete all the grid data for that column and job. Same with rows. So I tried to establish cascade deletes between COLUMNS>GRID_DATA and ROWS>GRID_DATA and that's where I got in trouble. I assume the reason is that if the user deletes a job, it's going to delete the columns and rows, and I've got multiple cascade paths coming from COLUMNS and ROWS to GRID_DATA. I get it.
The question is, what can I do about it? I don't have a cascade delete between JOBS>GRID_DATA, although I do want that behavior, but I was assuming it just would flow through COLUMNS or ROWS. I haven't bothered to add that key as I'm guessing that would just make the situation worse. Anyone got any advice for me on this one?
Hi. I am trying to log (Not SQL Transaction log) all deleted rowsinto a custome log file after performing "cascade delete".I couldn't find any reference for this in SQL manual or News group.Byung Choi
Hi, I have a Configuration Table tblConfig with 45 fields- PK_Config_Id ConfigCreatedOn ConfigEditedOn ConfigStartDate ConfigEndDate ConfigFor €¦. €¦. €¦.
And an Employee table tblEmployee PK_Emp_Id EmpName €¦. €¦. €¦.
I would create a Configuration and then apply this config to some Employees €” So, I created a table tblEmployee_Config with 2 fields FK_Config_Id FK_Emp_Id
But I MAY need to change(Edit) few of the properties(fields) of the Configuration record associated to an emp. Therefore I need all the fields of tblConfig table along with employee key in tblEmployee_Config So, I modified tblEmployee_Config to €” FK_Emp_Id FK_Config_Id ConfigCreatedOn ConfigEditedOn ConfigStartDate ConfigEndDate ConfigFor €¦. €¦. €¦. i.e. putting all the fields of the tblConfig into tblEmployee_Config along with emp key
But instead of having 2 almost similar tables (tblConfig and tblEmployee_Config), I merged both the tables into 1 table having Configurations as well as Employees associated to Configs as€” PK_Config_Id FK_Emp_Id FK_Config_Id ConfigCreatedOn ConfigEditedOn ConfigStartDate ConfigEndDate ConfigFor €¦. €¦. €¦. Where FK_Emp_Id and FK_Config_Id would be NULL for Configurations.
Here record 1 is a Configuration and records 2 and 3 are Emps records that are associated to Config 1.
Now my question is €“ 1. Is it correct if I put a relation like Primary key Foreign Key tblEmployee_Config tblEmployee_Config PK_Config_Id FK_Config_Id
2. Can I perform cascade delete on the same table tblEmployee_Config such that€“ When record 1 is deleted, records 2 and 3 should also get deleted because they refer to the first record?
I Use SQL Server 2005 Express edition. I have a few tables, which are inter-related. For e.g. I have a project table (ProjectID, Name, ...)
I also have a Project invoice table, in which ProjectID is referred as Foreign key.
Now, I want all the rows in Project invoice (child) table table to be deleted (for a particular ProjetID), if I delete the coresponding ProjectID in Projects (parent) table. If I use the 'on delete set null' or 'on delete cascade' constraint, only the ProjectID (in child table) is nullified whereas rest of the columns have data.
Is there any way out, where the whole ROW (not just that field) is deleted? Or, is it that this can be achieved only by some stored procedure / external program?
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
