Insert Trigger之后与用户和employeeID建立链接 [英] After Insert Trigger to establish the link with the user and the employeeID

问题描述

您好

我有下表。我正在尝试 写下触发器来插入   中的记录在插入或修改记录时的goUserEmployeeLink表中员工表中带有New DepotNo的"DepotNo"列。我必须建立
指向存储在表DefaultMapping中的所有用户的链接 随着 新员工   新添加的或者DepotNo列中有任何更改。

I have the following table . I am trying to  write the trigger to insert  the record in  in goUserEmployeeLink table when the record are inserted or Modified  the column 'DepotNo' with New DepotNo in Employee Table. I have to establish the link to all users stored in the table DefaultMapping  with the  new employee   which are newly added or if there is any change in the DepotNo column.

CREATE TABLE [dbo].[Employee](
	[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
	[DepotNo] [int] NOT NULL )


CREATE TABLE [dbo].[UserDefaultMapping](
	[LinkID] [int] IDENTITY(1,1) NOT NULL,
	[UserID] [int] NOT NULL,
	[DepotNo] [int] NULL)


CREATE TABLE [dbo].[UserEmployeeLink](
	[UserID] [int] NOT NULL DEFAULT ((0)),
	[DepotNo] [int] NOT NULL DEFAULT ((0)),
	[EmployeeID] [int] NOT NULL DEFAULT ((0)))

我有以下内容在UserDefaultMapping中记录

I have the following record in UserDefaultMapping

所以当我插入新记录时   或已更改现有的  depotno与新的DepotNo 然后，在UserEmployeeLink表中自动创建链接。例如，我正在插入新记录   在Employee
表中作为EmployeeID  = 300 和DepotNo = 1 然后 插入或更改DepotNo列之后 在员工表中，  

So When I insert new record   or have changed existing  depotno with new DepotNo  of the employee , then then the link automatically created in the table UserEmployeeLink . For example I am inserting the new record   in Employee table as EmployeeID  = 300  and DepotNo = 1  Then  after insert or change DepotNo column  in employee table,  

必须为所有用户创建链接  "映射"表中的DepotNo列为空，并且还应该为用户创建链接 具有 那个特定的DepotNo在Mapping表中。所以这种情况下应该插入链接 
到UserEmployeeLink 对于用户1,3和5.所以记录 必须创建 在调用触发器后的UserEmployeeLink中 插入员工表

The link have to be created to the all users where  the DepotNo column is null in Mapping table and also the link should be created to the users  having  that Particular DepotNo in Mapping table. So this case the link should be inserted  to the UserEmployeeLink  for the users 1, 3, and 5. So the record  have to be created  in UserEmployeeLink after calling trigger in the  Insert of employee table

当我将库从1更改为  2为员工300， 链接也应该建立 对于UserEmployeeLink表中的用户 如果不存在记录 对于那个  UserEmploeeLink中的user，depot和employeeID

Also When I change the depot no from 1 to  2 for the employee 300, the  link should also be established  for the users in UserEmployeeLink table  if not exist the record  for that  user,depot and employeeID in UserEmploeeLink

请帮助我解决问题

Pol

polachan

推荐答案

你对RDBMS的整个方法是完全错误的。根据定义，不是通过选项，表必须有一个键。标识列是表属性，根据定义，不是选项永远不能是正确的键。术语"链接"可以追溯到旧的网络
数据库，这是您在SQL中编写的内容;它在RDBMS中没有地位。让我们尝试修复你发布的灾难哦，在我忘记标识符永远不是整数之前，因为你不对它们进行数学运算。这通常在数据建模中任何类
的前几天都有所涉及。 
$


为什么你认为仓库（听起来像一个实体）是员工的属性？我认为这是一种将员工分配到仓库的关系。当然，如果您仍然不了解RDBMS如何工作，您可能会犯这个错误。你还
可能无知，无法在RDBMS中使用术语映射。我正在尝试纠正你发布的垃圾



CREATE TABLE人员

（emp_id CHAR（10）NOT NULL PRIMARY KEY，

  ..）;
$
CREATE TABLE Depots

（depot_nbr CHAR（5）NOT NULL PRIMARY KEY，

  ..）;
$


CREATE TABLE Emp_Depot_Assignments

（emp_id CHAR（10）NOT NULL

  ; REFERENCES人员（emp_id）

  ON UPDATE CASCADE

  ON DELETE CASCADE，

depot_nbr CHAR（5） 

  REFERENCES Depots（depot_nbr）

  ON UPDATE CASCADE

  ON DELETE CASCADE，

  ...）;

我们不在RDBMS中使用术语"链接";这指的是在网络数据库中构建的指针链。但是你想要模仿它们。这就是您使用零作为标识符的默认值的原因。在RDBMS中，根据定义，标识符不是数字，
但是在指针链中，零可以用作NIL指针。您也不知道行和记录之间的差异  [原文如此]！这通常在任何RDBMS介绍的第一周都有报道。



>>所以当我插入新的记录时[原文如此]鸟或者使用Personnel的新depot_nbr更改了现有的depot_nbr，然后在表UserPersonnelLink中自动创建了链接[sic]。例如，我正在插入新记录  [原文如此]鸟人员表中的
为emp_id = 300和depot_nbr = 1然后在人员表中插入或更改depot_nbr列之后，<< 

我认为您正在尝试做什么我们称之为DRI，代表声明性参照完整性。看看我发布的DDL是因为你懒得去做或学习。 

Your whole approach to RDBMS is completely wrong. By definition, not by option, a table must have a key. An identity column is a table property and again, by definition, not by option can never be a proper key. The term "link" goes back to the old network databases, which is what you’re writing in SQL; it has no place in RDBMS. Let’s try and fix the disaster you posted oh, before I forget identifiers are never integers because you don’t do math on them. This is usually covered in the first few days of any class in data modeling. 

Why do you think the depot (sounds like an entity) is an attribute of an employee? I would think it’s a relationship in which an employee is assigned to a depot. Of course if you’re still not understanding how RDBMS works you might make that mistake. You also might be ignorant enough to use the term mapping in RDBMS. I’m trying to correct the crap you posted

CREATE TABLE Personnel
(emp_id CHAR(10) NOT NULL PRIMARY KEY,
 ..);
CREATE TABLE Depots
(depot_nbr CHAR(5) NOT NULL PRIMARY KEY,
 ..);

CREATE TABLE Emp_Depot_Assignments
(emp_id CHAR(10) NOT NULL
 REFERENCES Personnel (emp_id)
  ON UPDATE CASCADE
  ON DELETE CASCADE,
depot_nbr CHAR(5) 
 REFERENCES Depots(depot_nbr)
  ON UPDATE CASCADE
  ON DELETE CASCADE,
 …);
We do not use the terms "link" in RDBMS; this refers to pointer chains that were built in network databases. But yet you’re trying to mimic them. This is why you’re using the zero as a default value for an identifier. In RDBMS identifier is not numeric by definition, but in a pointer chain the zero can serve as a NIL pointer. You are also not aware the differences between rows and record  [sic] s! That’s usually covered in the first week of any introduction to RDBMS.

>> So When I insert new record  [sic]  or have changed existing depot_nbr with new depot_nbr of the Personnel , then the link [sic] automatically created in the table UserPersonnelLink . For example I am inserting the new record  [sic]  in Personnel table as emp_id = 300 and depot_nbr = 1 Then after insert or change depot_nbr column in Personnel table, << 
what you what I think you are trying to do is covered with what we call DRI which stands for declarative referential integrity. Look at the DDL that I posted because you were too lazy to do it or learned. 

这篇关于Insert Trigger之后与用户和employeeID建立链接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！