locked
After Insert Trigger to establish the link with the user and the employeeID RRS feed

  • Question

  • Hi

    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)))

    CONSTRAINT [PK_goUserEmployeeLink] PRIMARY KEY CLUSTERED 
    (
    [UserID] ASC,
    [DepotNo] ASC,
    [EmployeeID] ASC )

    I have the following record in UserDefaultMapping

    LinkID UserID DepotNo
    1 5
    2 10
    3 12 1
    4 14 2
    5 13 1

    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,  

    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

    UserID DepotNO EmployeeID
    1 1 300
    2 1 300
    3 1 300
    5 1 300

    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

    UserID DepotNO EmployeeID
    1 2 300
    2 2 300
    4 2 300

    Please can you help me to get the solution

    Pol


    polachan


    • Edited by polachan Friday, May 17, 2019 5:47 AM Enhancement
    Friday, May 17, 2019 5:44 AM

All replies

  • 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. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, May 17, 2019 7:00 PM
  • Hi Pol,

    When I read your post, I got stuck that I couldn't get complete scenario at that point. For example, you said that you did the update operation "change the depot no from 1 to  2 for the employee 300", so what are the three tables like before this operation? And after this operation, what would the three tables be like? And other operations?

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 20, 2019 9:50 AM
  • When I insert new record  in Employee Table

    Insert Into Employee(EmpID,DepotNo) Values (@EmpId,@UserID)

    Then I have to call After insert trigger to   insert the record  in  UserEmployeeLink using the sql

    Insert into  userEmployeeLink(Userid,depotno,EmpID) 

    select userid,@EmpID,@DepotNo from userdefaultMapping  // I have to give  the link to all the users defined in the table UserDefaultMapping

    Simillarly  I applied the following  update in Employee table, the trigger should be called 

    Update  Employee set Depotno=@DepotNo Where Depotno = 1 and EmpId = @EmpID ,

    then automatically the depotno should be changed  in the table  UserEmployeeLink . So I have to call the trigger on employee table for that changes

    Update useremployeelink set depot = inserted.depotnumber where empid = EmpId = @EmpID


    Please can you help to write the trigger 

     


    polachan


    • Edited by polachan Monday, May 20, 2019 12:57 PM Enhancement
    Monday, May 20, 2019 11:57 AM
  • I have given the reply thread any help would be very appreciated

    polachan

    Monday, May 20, 2019 12:57 PM
  • When I insert new record  in Employee Table

    Insert Into Employee(EmpID,DepotNo) Values (@EmpId,@UserID)

    Then I have to call After insert trigger to   insert the record  in  UserEmployeeLink using the sql

    Insert into  userEmployeeLink(Userid,depotno,EmpID) 

    select userid,@EmpID,@DepotNo from userdefaultMapping  // I have to give  the link to all the users defined in the table UserDefaultMapping

    Simillarly  I applied the following  update in Employee table, the trigger should be called 

    Update  Employee set Depotno=@DepotNo Where Depotno = 1 and EmpId = @EmpID ,

    then automatically the depotno should be changed  in the table  UserEmployeeLink . So I have to call the trigger on employee table for that changes

    Update useremployeelink set depot = inserted.depotnumber where empid = EmpId = @EmpID


    Please can you help to write the trigger 

     


    polachan


    OK.

    Based on your reply, is this the trigger that you need?

    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)),
    
    CONSTRAINT [PK_goUserEmployeeLink] PRIMARY KEY CLUSTERED 
    (
    	[UserID] ASC,
    	[DepotNo] ASC,	 
    	[EmployeeID] ASC 
    )
    )
    GO
    
    CREATE TRIGGER NewEmployee
    ON Employee
    AFTER INSERT 
    AS
    --When I insert new record  in Employee Table
    --Insert Into Employee(EmpID,DepotNo) Values (@EmpId,@UserID)
    --Then I have to call After insert trigger to   insert the record  in  UserEmployeeLink using the sql
    --Insert into  userEmployeeLink(Userid,depotno,EmpID) 
    --select userid,@EmpID,@DepotNo from userdefaultMapping  // I have to give  the link to all the users defined in the table UserDefaultMapping
    INSERT INTO UserEmployeeLink(UserID,DepotNo,EmployeeID)
    SELECT T.UserID,T1.EmployeeID,T1.DepotNo
    FROM UserDefaultMapping T
    CROSS JOIN inserted T1
    
    --Simillarly  I applied the following  update in Employee table, the trigger should be called 
    --Update  Employee set Depotno=@DepotNo Where Depotno = 1 and EmpId = @EmpID ,
    UPDATE T SET DepotNo=T1.DepotNo
    FROM Employee T
    INNER JOIN inserted T1 ON T.EmployeeID=T1.EmployeeID
    WHERE T.DepotNo=1
    
    --then automatically the depotno should be changed  in the table  UserEmployeeLink . So I have to call the
    --trigger on employee table for that changes 
    --Update useremployeelink set depot = inserted.depotnumber where empid =
    --EmpId = @EmpID
    UPDATE T SET DepotNo=T1.DepotNo 
    FROM UserEmployeeLink T
    INNER JOIN inserted T1 ON T.EmployeeID=T1.EmployeeID
    
    GO

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 23, 2019 7:11 AM