none
Trigger to input a warranty date based on date received

    Question

  • I am trying to Create a Trigger, that uses a DateReceived and adds on (x) amount of years into a Warranty_date field depending on a WHERE statement. For example - IF HardwareID= '1' AND DateReceived = '2010' THEN DateReceived + (4)

    AND INPUT WARRANTY_DATE (4).

    HardwareID '1' = Laptops    Warranty of 4 Years

    HardwareID '2' = PC            Warranty of 4 Years

    HardwareID '3' = Monitor    Warranty of 1 Years

    My Code so far - 

    CREATE TRIGGER trgMainTblInsert
       ON  tblasset
       INSTEAD OF INSERT
    AS 
    BEGIN
    SET NOCOUNT ON;
    INSERT INTO tblasset (
          DateReceived ,
            warranty_date

        ) SELECT
            DateRecieved,
            DATEADD(yy,3,DateReceived)

         FROM tblasset
            INSERTED

                   
    Friday, June 28, 2013 8:30 AM

Answers

  • Hi rustynails28,

    We can create a stored procedure to achieve our function, for example:

    create table tblWarranty_Inventory
    (
    	HardwareID int not null,
    	DateReceived date not null,
    	Warranty_date date
    )
    
    --drop proc UpdateWarranty;
    
    -- Please execute the following codes seperately
    create proc dbo.UpdateWarranty
    @HardwareID int,
    @DateReceived date
    as
    insert into tblWarranty_Inventory(HardwareID,DateReceived,Warranty_date) values(
    	@HardwareID,@DateReceived,
    	case @HardwareID
    	when 1
    	then DATEADD(yy, 3, @DateReceived)
    	when 2
    	then DATEADD(yy, 3, @DateReceived)
    	when 3
    	then DATEADD(yy, 1, @DateReceived)
    	end
    )
    
    
    declare @DateReceived date;
    set @DateReceived = GETDATE()
    exec dbo.UpdateWarranty @HardwareID=1,@DateReceived=@DateReceived
    
    select * from tblWarranty_Inventory
    


    Allen Li
    TechNet Community Support

    Sunday, June 30, 2013 2:34 AM
    Moderator
  • Now you need an INSERT, not update, so

    CREATE TRIGGER trgMainTblInsert ON tblAsset AFTER INSERT AS BEGIN SET NOCOUNT ON; INSERT INTO tblWarranty_Inventory (AssetID, Warranty_Expiry ) SELECT AssetId, CASE WHEN HardwareID IN ('1','2')

    THEN DATEADD(year, 3, DateReceived) WHEN HardwareID = '3'

    THEN DATEADD(year, 1, TA.DateReceived) END FROM INSERTED END GO



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Sunday, June 30, 2013 3:47 AM
    Moderator

All replies

  • You can achieve this easily by creating AFTER (FOR) trigger. code:

    CREATE TRIGGER trgMainTblInsert
       ON  tblasset
       FOR INSERT
    AS 
    BEGIN
    SET NOCOUNT ON;
    
    UPDATE	TA
    SET		TA.warranty_date =
    		CASE WHEN HardwareID = '1' THEN DATEADD(yy, 3, INSERTED.DateReceived)
    			 WHEN HardwareID = '2' THEN DATEADD(yy, 3, INSERTED.DateReceived)
    			 WHEN HardwareID = '3' THEN DATEADD(yy, 1, INSERTED.DateReceived)
    		END
    FROM	tblasset TA
    INNER JOIN INSERTED ON TA.PrimaryKeyColumn = INSERTED.PrimaryKeyColumn


    - Vishal

    SqlAndMe.com

    Friday, June 28, 2013 8:43 AM
  • thanks for your reply what change do I need to do on the line

    INNER JOIN INSERTED ON TA.PrimaryKeyColumn = INSERTED.PrimaryKeyColumn


    Friday, June 28, 2013 8:54 AM
  • thanks for your reply what change do I need to do on the line

    INNER JOIN INSERTED ON TA.PrimaryKeyColumn = INSERTED.PrimaryKeyColumn


    you need your Primary Key column or identity column there.

    - Vishal

    SqlAndMe.com

    Friday, June 28, 2013 9:03 AM
  • Thanks but not working, where am I going wrong. I have placed the Primary Key ID of where the warranty column is in.

    INNER JOIN INSERTED ON TA.AssetID = INSERTED.AssetID

    I am getting a error of 

    Msg 102, Level 15, State 1, Procedure trgMainTblInsert, Line 15
    Incorrect syntax near 'AssetID'.

    Friday, June 28, 2013 9:19 AM
  • Thanks but not working, where am I going wrong. I have placed the Primary Key ID of where the warranty column is in.

    INNER JOIN INSERTED ON TA.AssetID = INSERTED.AssetID

    I am getting a error of 

    Msg 102, Level 15, State 1, Procedure trgMainTblInsert, Line 15
    Incorrect syntax near 'AssetID'.

    missed end... :)

    CREATE TRIGGER trgMainTblInsert
       ON  tblasset
       FOR INSERT
    AS 
    BEGIN
    SET NOCOUNT ON;
    
    UPDATE	TA
    SET		TA.warranty_date =
    		CASE WHEN TA.HardwareID = '1' THEN DATEADD(yy, 3, TA.DateReceived)
    			 WHEN TA.HardwareID = '2' THEN DATEADD(yy, 3, TA.DateReceived)
    			 WHEN TA.HardwareID = '3' THEN DATEADD(yy, 1, TA.DateReceived)
    		END
    FROM	tblasset TA
    INNER JOIN INSERTED ON TA.AssetID = INSERTED.AssetID
    END



    - Vishal

    SqlAndMe.com

    Friday, June 28, 2013 11:14 AM
  • I adjusted the code to match my database, for example I have decided it would be better to have a warranty table rather than just have a field in the main table. So I have created ' tblWarranty_Inventory ' and the field I need to insert the date into is ' tblWarranty_Inventory.Warranty_Expiry' but I am getting an error .

    Msg 4104, Level 16, State 1, Procedure trgMainTblInsert, Line 8
    The multi-part identifier "tblWarranty_Inventory.Warranty_Expiry" could not be bound.

    CREATE TRIGGER trgMainTblInsert
       ON  tblasset
       FOR INSERT
    AS 
    BEGIN
    SET NOCOUNT ON;

    UPDATE TA
    SET tblWarranty_Inventory.Warranty_Expiry =
    CASE WHEN TA.HardwareID = '1' THEN DATEADD(yy, 3, TA.DateReceived)
    WHEN TA.HardwareID = '2' THEN DATEADD(yy, 3, TA.DateReceived)
    WHEN TA.HardwareID = '3' THEN DATEADD(yy, 1, TA.DateReceived)
    END
    FROM tblasset TA
    INNER JOIN INSERTED ON TA.AssetID = INSERTED.WarrantyID
    END


    Friday, June 28, 2013 1:13 PM
  • Hi rustynails28,

    We can create a stored procedure to achieve our function, for example:

    create table tblWarranty_Inventory
    (
    	HardwareID int not null,
    	DateReceived date not null,
    	Warranty_date date
    )
    
    --drop proc UpdateWarranty;
    
    -- Please execute the following codes seperately
    create proc dbo.UpdateWarranty
    @HardwareID int,
    @DateReceived date
    as
    insert into tblWarranty_Inventory(HardwareID,DateReceived,Warranty_date) values(
    	@HardwareID,@DateReceived,
    	case @HardwareID
    	when 1
    	then DATEADD(yy, 3, @DateReceived)
    	when 2
    	then DATEADD(yy, 3, @DateReceived)
    	when 3
    	then DATEADD(yy, 1, @DateReceived)
    	end
    )
    
    
    declare @DateReceived date;
    set @DateReceived = GETDATE()
    exec dbo.UpdateWarranty @HardwareID=1,@DateReceived=@DateReceived
    
    select * from tblWarranty_Inventory
    


    Allen Li
    TechNet Community Support

    Sunday, June 30, 2013 2:34 AM
    Moderator
  • Now you need an INSERT, not update, so

    CREATE TRIGGER trgMainTblInsert ON tblAsset AFTER INSERT AS BEGIN SET NOCOUNT ON; INSERT INTO tblWarranty_Inventory (AssetID, Warranty_Expiry ) SELECT AssetId, CASE WHEN HardwareID IN ('1','2')

    THEN DATEADD(year, 3, DateReceived) WHEN HardwareID = '3'

    THEN DATEADD(year, 1, TA.DateReceived) END FROM INSERTED END GO



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Sunday, June 30, 2013 3:47 AM
    Moderator