Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 121
  • Last Modified:

Change inserted value on trigger

Hi guys,

Here is a section of my trigger...

INSERT INTO XYZ (SupplierCode, ActiveStatus, DateUpdated, UpdatedBy)
    SELECT
         i.Supplier, i.UserField2,CURRENT_TIMESTAMP, SYSTEM_USER
    FROM
         inserted as i LEFT OUTER JOIN [TriggerTablesAscent].[dbo].[SupplierStatus] tta ON i.Supplier = tta.SupplierCode

Open in new window


My ActiveStatus field is stored as bit. I have just noticed the UserField2 is a single char of Y or N. I need to convert Y into True and N into False before the insert.

How is it best to achieve this?

Thanks,
Dean.
0
deanlee17
Asked:
deanlee17
  • 2
  • 2
  • 2
1 Solution
 
ste5anSenior DeveloperCommented:
Use a CASE expression like CASE I.UserField2 WHEN = 'Y' THEN 1 WHEN 'N' THEN 0 END or IIF() when your on SQL Server 2012+.
0
 
deanlee17Author Commented:
Would the case statement sit above INSERT INTO?
0
 
Lee SavidgeCommented:
No, it would be inline.

If you want True and False, then...

INSERT INTO XYZ (SupplierCode, ActiveStatus, DateUpdated, UpdatedBy)
    SELECT
         i.Supplier,CASE I.UserField2 WHEN 'Y' THEN 'True' ELSE 'False' END,CURRENT_TIMESTAMP, SYSTEM_USER
    FROM
         inserted as i LEFT OUTER JOIN [TriggerTablesAscent].[dbo].[SupplierStatus] tta ON i.Supplier = tta.SupplierCode

Open in new window


If you want 1 or 0 then...

INSERT INTO XYZ (SupplierCode, ActiveStatus, DateUpdated, UpdatedBy)
    SELECT
         i.Supplier,CASE I.UserField2 WHEN 'Y' THEN 1 ELSE 0 END,CURRENT_TIMESTAMP, SYSTEM_USER
    FROM
         inserted as i LEFT OUTER JOIN [TriggerTablesAscent].[dbo].[SupplierStatus] tta ON i.Supplier = tta.SupplierCode

Open in new window


If you're going to get technical about it, then true should be -1 and false should be 0 (or not true, or everything else). What specific data types do you want? An integer or a string?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
ste5anSenior DeveloperCommented:
Nope. Read the link and my post again. CASE is an expression not a statement.
0
 
deanlee17Author Commented:
Thanks for the feedback. Lee that worked for the case above, but should it also work for an update? My Insert works but my update fails:

  SET
        ActiveStatus = CASE i.UserField2 WHEN 'Y' THEN 'True' ELSE 'False' END,CURRENT_TIMESTAMP, SYSTEM_USER, DateUpdated = CURRENT_TIMESTAMP, UpdatedBy = SYSTEM_USER

Open in new window


The True or False is going into a bit field.

Thanks,
Dean
0
 
Lee SavidgeCommented:
Then use 1 and 0 as true and false.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now