Results 1 to 15 of 15

Thread: Trigger problem

  1. #1
    Join Date
    Oct 2003
    Posts
    8

    Unanswered: Trigger problem

    Hi All

    I am new to SQL Server and am attempting to put together a Trigger that stamps the Date/Time when the record was inserted or updated.

    The only problem is that the trigger doesnt seem to work :-(

    Any help would be great:

    The trigger code:

    CREATE TRIGGER trg_UPD_C_Web_Account
    ON C_Web_Account
    FOR UPDATE, INSERT
    AS
    DECLARE @ID Int
    SELECT @ID = C_Web_AC_ID FROM C_Web_Account
    UPDATE C_Web_Account
    SET Last_Update = getdate()
    WHERE C_Web_AC_ID = @ID

    Here is the create statement for the table:
    CREATE TABLE C_Web_Account (
    C_Web_AC_ID VarChar(10),
    AC_Status_ID SmallInt FOREIGN KEY REFERENCES AC_Status(AC_Status_ID),
    AC_Password VarChar(15),
    Init_Date DateTime,
    Init_IP VarChar(15),
    Last_Login_Date DateTime,
    Last_IP VarChar(15),
    Curr_Login_Date DateTime,
    Curr_Login_IP VarChar(15),
    Notes_ID Int,
    Last_Update DateTime)

  2. #2
    Join Date
    Jan 2004
    Location
    INDIA
    Posts
    9
    hi Dear

    CREATE TRIGGER trg_UPD_C_Web_Account
    ON C_Web_Account
    FOR UPDATE, INSERT
    AS
    DECLARE @ID Int
    SELECT @ID = C_Web_AC_ID FROM C_Web_Account
    UPDATE C_Web_Account
    SET Last_Update = getdate()
    WHERE C_Web_AC_ID = @ID


    Replace your above trigger with following one.
    AND CHECK IT WHEATHER IT FULLFILL YOUR REQUIREMENT ........

    CREATE TRIGGER trg_UPD_C_Web_Account
    ON C_Web_Account
    FOR UPDATE, INSERT
    AS
    DECLARE @ID Int
    SELECT @ID = INS.C_Web_AC_ID FROM insert ed AS INS
    UPDATE C_Web_Account
    SET Last_Update = getdate()
    WHERE C_Web_AC_ID = @ID
    Last edited by DJSAKARIYA; 02-15-04 at 00:59.

  3. #3
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    If you have the possibility of having multiple updates at once you can use this:

    CREATE TRIGGER trg_UPD_C_Web_Account
    ON C_Web_Account
    FOR UPDATE, INSERT
    AS
    UPDATE C_Web_Account
    SET Last_Update = getdate()
    WHERE C_Web_AC_ID in (select C_Web_AC_ID from inserted)

    HTH
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  4. #4
    Join Date
    Oct 2003
    Posts
    8
    Thanks guys,

    both of these work a treat...

    But i forgot to add that I need to add to the trigger: if its an Insert and the field: C_Web_AC_ID is blank i need to inciment the ID.

    The ID must start with a 'W' and have 9 numbers after it, the initial one will be 'W100000000' (the seed also needs to be added to the trigger)

    But if an ID was added during the insert statement: it does not need to add one.

    I dont quite know where to start with this one.

    Thanks

  5. #5
    Join Date
    Jan 2004
    Location
    INDIA
    Posts
    9
    HI,
    MODIFY YOUR TRIGGER WITH FOLLOWING CODE
    AND CHECK IT WHETHER IT FULLFILL YOUR REQUIREMENT

    CREATE TRIGGER trg_UPD_C_Web_Account
    ON C_Web_Account
    FOR UPDATE, INSERT
    AS
    DECLARE @ID Int

    SELECT @ID = INS.C_Web_AC_ID FROM insert ed AS INS

    IF @ID IS NULL
    select @ID=max(C_WEB_AC_ID,9)) FROM C_WEB_ACCCOUNT

    UPDATE C_Web_Account
    SET Last_Update = getdate()
    WHERE C_Web_AC_ID = @ID

    WITH REGARDS,
    DHIRAJ

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    DJSAKARIYA, you need to read rhigdon's post. Your logic will crash and burn if more than one record is inserted.

    bernie123,
    You need to put some more thought into your application design. You are in for trouble if you try to have your database assign unique IDs to a dataset at the same time secondary processes are assigning IDs. Keeping duplicates from appearing will be difficult.

    One suggestion you should consider is to change your ID field to an integer value, rather than CHAR(10), and then concatenate it as a string to "W1" when necessary. You can even create it as a calculated field on your table. For instance, create a unique integer value to store raw ID values (name it something like "C_Raw_ID int") and then make your C_Web_AC_ID a calculate field:

    'W1' + right('00000000' + cast(C_Raw_ID as varchar(8)), 8)

    As far as keeping your ID generators from crashing into eachother, is it possible to add another character to your ID field that indicates its source? That way multiple generators could create IDs and still keep them unique.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Oct 2003
    Posts
    8
    Hi blindman

    The 'W' identifies where the data is created, all other sites that generate ID's will have a different inital letter.

    My only worry with the C_Raw_ID is when one of our remote databases creates an ID it wont be unique in this field when the data is copied across.

    Or do you mean that the C_Raw_ID is independent of the C_Web_Account table.

    Sorry I may have missed your point.. please could you clarify

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    C_RAW_ID won't be unique, but the combination of C_RAW_ID + the origination site will be. Make the combination a unique key.

    Then, let's say your local site's designation is 'W'. You can get a new ID by using

    select max(C_RAW_ID) + 1 from C_Web_Account where LocationCode = 'W'
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Oct 2003
    Posts
    8
    I think i have the right idea, i have put together this trigger which seems to have multiple syntax errors.


    CREATE TRIGGER trg_UPD_Client_Details
    ON Client_Details
    FOR UPDATE, INSERT
    AS
    IF Inserted THen
    IF IsNull(SELECT Client_ID FROM Inserted) = True THen
    Update Client_Details
    SET Client_ID = 'W' + RIGHT('00000000' + cast((SELECT MAX(C_Raw_ID) + 1 FROM C_Web_Account WHERE LocationCode = 'W')as varchar(9)), 8)
    SET C_Raw_ID = (SELECT Int(Right(Client_ID, 8) FROM Inserted))
    SET LocationCode = 'W'
    End IF
    End IF
    UPDATE Client_Details
    SET Last_Update = getdate()
    WHERE Client_ID IN (SELECT Client_ID FROM INSERTED)
    GO

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yup. It does.

    "Inserted" is a table (a virtual one), so "IF Inserted" won't work.

    Because it is a table holding potentially more than one record, "IF IsNull(SELECT Client_ID FROM Inserted) = True " can't be evaluated because more than one value could be returned by the inner clause. And there is no "True" value in TSQL.

    There's more, too.

    You need to read up on SQL Programming or find someone in your office to help you. Also, did you revise your table design to split the ID into two parts? You will need to assign the C_Raw_ID before you reference it for Client_ID.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Oct 2003
    Posts
    8
    Hi

    I have modified the query and the table a little to take on board what you have said. The raw ID is now the C_Web_AC_ID - this is an IDENTITY Int Field, teh Web_Ac_Login field is now the field that i wish to incriment with the location and raw_id/c_web_Ac_id. I have added the create statement to confirm this.

    Its not working, but i was wondering if the select is setup in an approprate mannor to return the value of NULL for the single record? Or am i in the same situation as before?

    CREATE TRIGGER trg_UPD_C_Web_AC
    ON C_Web_AC
    FOR UPDATE, INSERT
    AS
    DECLARE @ID Int, @Login VarChar(10)
    SELECT @ID = Max(i.C_Web_AC_ID), @Login = i.web_ac_login FROM Inserted i Inner JOIN c_web_ac c ON c.C_Web_AC_ID = i.C_Web_AC_ID GROUP BY i.C_Web_AC_ID, i.web_ac_login
    IF (@Login = NULL)
    BEGIN
    Update C_Web_AC
    SET LocationCode = 'W',
    Web_AC_Login = 'W' + RIGHT('00000000' + cast(@ID as varchar(9)), 8),
    Last_Update = getdate()
    WHERE C_Web_AC_ID = @ID
    END
    ELSE
    BEGIN
    UPDATE C_WEB_AC
    SET Last_Update = getdate()
    WHERE C_Web_AC_ID = @ID
    END
    GO


    CREATE TABLE C_Web_AC (
    C_Web_AC_ID Int IDENTITY CONSTRAINT pk_C_Web_AC_ID PRIMARY KEY,
    AC_Status_ID SmallInt FOREIGN KEY REFERENCES AC_Status(AC_Status_ID),
    Web_AC_Login VarChar(10),
    LocationCode Char(1),
    AC_Password VarChar(15),
    Init_Date DateTime,
    Init_IP VarChar(15),
    Last_Login_Date DateTime,
    Last_IP VarChar(15),
    Curr_Login_Date DateTime,
    Curr_Login_IP VarChar(15),
    Notes_ID Int,
    Last_Update DateTime)
    GO

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I don't know that you can use an identity field (which automatically increments) while trying to insert data that already has an ID value set.

    You can explicitly insert values using the SET IDENTITY_INSERT command, but then you will have to check yourself to make sure the value do not already exist, and if they do exist you will need to handle the situation.

    I think your best bet is to avoid using IDENTITY and set the ID yourself using the SET NewID = (Select MAX(CurrentID)+1 from YourTable where PrefixClause = 'W' syntax.

    Other people on this forum with more experience using identity fields (I avoid them) may be able to give you other suggestions.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Oct 2003
    Posts
    8
    Sorry I didnt explain properly.

    My aim now is to have an automatically assigned C_Web_AC_ID for every online web account. This is not the equivelent of C_Raw_ID and will be used to assign the Web_AC_Login of client accounts created from the internet. This ID will look like - "W000045534".

    Web accounts that are setup by remote databases, ie in-house, will be assigned a web_ac_ID where they are created with a different inital charactor so that we have no integrity problems.

    When the data is syncronised from the in-house databases to/with the web database we a C_Web_AC_ID will be assigned to the account but the trigger will recognise that the Web_AC_Login is not null and will therefor not assign an a Web_AC_Login.

    The only problem is that the trigger i have written does not recognise when the Web_AC_Login IS NULL :-)

    Cheers

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can't compare null values using the = sign. Such comparisons always evaluate to false. You should use the "is null" syntax. Try this code:

    declare @Login varchar(10)
    if @Login = Null Print '= sign works'
    if @Login = @Login Pring 'self-comparison works' --can't even compare to self!
    if @Login is null Print 'is null syntax works'
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Oct 2003
    Posts
    8
    Thanks that was ideal, i have changed to IS NULL and it works a treat.

    Thanks for all of your help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •