Trigger only inserting 1 character of variable into table

Question

I have the following sql trigger that is inserting data into a table named PS_AUDIT_PSROLEUSR, based on a Delete action that occurred on a related table.

When the table update is triggered by an online user in the application (a role row is removed) then the entire OPRID is inserted correctly into PS_AUDIT_PSROLEUSR, however when the triggers runs via a batch program I have written, it is only getting the first letter of the AUDIT_OPRID. I have confirmed this by running the program under another OPRID, and it still only inserts the first character into the column. Forgive me that I am not very familiar with SQL triggers.

ALTER TRIGGER [dbo].[PSROLEUSER_TR] 
ON [dbo].[PSROLEUSER]
FOR INSERT, UPDATE, DELETE
AS
    SET NOCOUNT ON

    DECLARE @XTYPE CHAR(1), @OPRID CHAR(30)

    SET @OPRID = NULL

    SELECT @OPRID = CASE(CHARINDEX(',', CAST(context_info AS CHAR(128))))
                       WHEN 0 THEN 'Native SQL'
                       ELSE SUBSTRING(CAST(context_info AS CHAR(128)), 1, (CHARINDEX(',', CAST(context_info AS CHAR(128)))-1))
                    END
    FROM sys.sysprocesses
    WHERE spid = @@spid

    -- Determine Transaction Type
    IF EXISTS (SELECT * FROM DELETED)
    BEGIN
        SET @XTYPE = 'D'
    END

    IF EXISTS (SELECT * FROM INSERTED)
    BEGIN
        IF (@XTYPE = 'D')
        BEGIN
            SET @XTYPE = 'U'
        END
        ELSE
        BEGIN
            SET @XTYPE = 'I'
        END
    END

    -- Transaction is a Delete
    IF (@XTYPE = 'D')
    BEGIN
        INSERT INTO PS_AUDIT_PSROLEUSR (AUDIT_OPRID, AUDIT_STAMP, AUDIT_ACTN, ROLEUSER, ROLENAME, DYNAMIC_SW)
            SELECT 
                @OPRID, 
                DATEADD(SECOND, ROW_NUMBER() OVER (ORDER BY @OPRID), GETDATE()), 
                'D', ROLEUSER, ROLENAME, DYNAMIC_SW 
            FROM 
                deleted 
    END

    -- Transaction is a Insert
    IF (@XTYPE = 'I')
    BEGIN
        INSERT INTO PS_AUDIT_PSROLEUSR (AUDIT_OPRID, AUDIT_STAMP, AUDIT_ACTN, ROLEUSER, ROLENAME, DYNAMIC_SW)
             SELECT 
                 @OPRID, GETDATE(),
                 'A', ROLEUSER, ROLENAME, DYNAMIC_SW 
             FROM 
                 inserted 
    END

    -- Transaction is a Update
    IF (@XTYPE = 'U')
    BEGIN
        -- Before Update
        INSERT INTO PS_AUDIT_PSROLEUSR (AUDIT_OPRID, AUDIT_STAMP, AUDIT_ACTN, ROLEUSER, ROLENAME, DYNAMIC_SW)
            SELECT 
                @OPRID, GETDATE(), 'K',
                ROLEUSER, ROLENAME, DYNAMIC_SW 
            FROM 
                deleted 
        -- After Update
        INSERT INTO PS_AUDIT_PSROLEUSR (AUDIT_OPRID, AUDIT_STAMP, AUDIT_ACTN, ROLEUSER, ROLENAME, DYNAMIC_SW)
            SELECT 
                @OPRID, GETDATE(), 'N',
                ROLEUSER, ROLENAME, DYNAMIC_SW 
            FROM 
                inserted 
    END

EXAMPLE OF ROW INSERTED IN PS_AUDIT_PSROLEUSR

AUDIT_OPRID AUDIT_STAMP              AUDIT_ACTN ROLEUSER    ROLENAME    DYNAMIC_SW
K           2019-04-25 08:33:08.340  D          LTESTUSER   EUSER       N
K           2019-04-25 08:33:09.340  D          LTESTUSER   EPRO        N

I can't seem to access the 'DELETE' table in the SELECT * FROM DELETED so I'm not sure if this is just dynamically generated at run time only.

Side note - whats kind of odd is that is I query PS_AUDIT_PSROLEUSR as follows for the value 'K' I get 0 rows returned. I've checked that there are no trailing spaces behind the letter.

    SELECT *
    FROM PS_AUDIT_PSROLEUSR
    WHERE AUDIT_OPRID = 'K'

I only get data for 'K' if I use the LIKE operator

-- Results in 0 rows

EDIT: If I run the following code I am getting 12 character length for my row inserted with 'K' so something is adding extra trailing spaces...

SELECT LEN(AUDIT_OPRID),*
FROM PS_AUDIT_PSROLEUSR
WHERE AUDIT_OPRID  like 'K%'

I've also tried adding this simple IF statement to the trigger, however it does not seem to work either: I thought doing the conversion from binary (context_info) to varchar would allow the logic to filter out the value 'K'.

IF (CONVERT(VARCHAR(256),@OPRID)) <> 'K' 
BEGIN

The trailing spaces is most likely created by the CHAR data type. CHAR(30) for example will pad your value with spaces so that the length is always 30 characters long. If you do not want them to have the extra spaces, you would want to use VARCHAR(30) . Side note - if you plan on using Unicode characters NVARCHAR(30) would be used. (The "N" in the front indicates that it will allow Unicode characters). — Wiz, Apr 25 '19 at 18:45
This weird thing is that this currently works fine the way it is when user's are updating data in the application themselves, it inserts the entire OPRID into the column. It is just exhibiting this behavior when the data is updated by the batch program. I don't plan on outputting any unicode characters. — Nick, Apr 25 '19 at 19:27
I can't see anything that would cause this in the code that you have above. Please provide the code that you are using to set the context info in your batch program. My guess is that you're not setting it correctly or it is somehow getting truncated there. — pcdev, Apr 25 '19 at 21:03
And as a side note, yes, the `inserted` and `deleted` tables are dynamically created and populated and are only available in certain contexts, [triggers](https://learn.microsoft.com/en-us/sql/relational-databases/triggers/use-the-inserted-and-deleted-tables?view=sql-server-2017) being one of them. — pcdev, Apr 25 '19 at 21:06
@pcdev I'll have to do more digging as I'm honestly not sure how how the program does this, it must be done at a lower level than what I am coding. — Nick, Apr 26 '19 at 12:23
There appears to be a bug in the user application where 3rd party programs like mine are not setting the correct contextual information. As a work around, at the beginning of the trigger procedure (after @OPRID is defined) Can I put in code to say if the length of the @OPRID is equal to 1 (`IF EXISTS (SELECT LEN(@OPRID) = 1`) then (code to exit trigger)? What would be the code to exit the trigger? If this is possible, then in my batch program I can do my own insert independently with the correct OPRID value. — Nick, Apr 26 '19 at 15:08

score 3 · Accepted Answer · answered Apr 30 '19 at 11:41

This is not a necessarily a problem with the trigger but the character encoding of the CONTEXT_INFO binary value when set by the batch application. You will get these symptoms when the CONTEXT_INFO value is actually a Unicode string and the binary value is subsequently cast as CHAR in T-SQL.

Consider this example:

DECLARE @ContextInfo varbinary(128) = CAST(N'ABC,DEF,GHI' AS varbinary(128));
SET CONTEXT_INFO @ContextInfo;
GO

DECLARE @OPRID CHAR(30) = NULL;
SELECT CAST(context_info AS CHAR(30)) AS CharValue, CAST(context_info AS NCHAR(30)) AS NCharValue 
FROM sys.sysprocesses
WHERE spid = @@spid;
GO

Results:

+-----------+-------------+
| CharValue | NCharValue  |
+-----------+-------------+
| A         | ABC,DEF,GHI |
+-----------+-------------+

To address the issue, you could either change the application code to set CONTEXT_INFO to an ANSI string or change the T-SQL CASE expression to CAST the value as NCHAR. Below is T-SQL example that does this and also uses the sys.dm_exec_sessions DMV instead of sys.processes, which has been deprecated for 15 years.

SELECT @OPRID = CASE(CHARINDEX(',', CAST(context_info AS NCHAR(64))))
                   WHEN 0 THEN N'Native SQL'
                   ELSE SUBSTRING(CAST(context_info AS NCHAR(64)), 1, (CHARINDEX(',', CAST(context_info AS NCHAR(64)))-1))
                END
FROM sys.dm_exec_sessions
WHERE session_id = @@spid;

Trigger only inserting 1 character of variable into table

1 Answers1